title: MS SQL-Server: cannot ODBC- connect on Windows 7?
body: <p>I have a problem creating an ODBC data source connection for SQL-Server on Windows 7. </p>
  
  <p>Problem summary: ODBC datasource connection can be created for the "master" database only, not for an additional, arbitrary database.</p>
  
  <p>I have googled around the Net and haven't found a solution. Here are the details:</p>
  
  <ol>
  <li>I have MS SQL Server installed on a Windows 7 64-bit system. Details: SQLEXPRESS edition, version: 10.1.2531.0, build: 2007.100.2531.0, Instance ID: MSSQL10.SQLEXPRESS.</li>
  </ol>
  
  <p>Using "SQL Server Configuration Manager", it says that: <pre>SQL Server(SQLEXPRESS) running</pre>
  I have also set up: "Log on as: built-in account -> Local System".</p>
  
  <ol>
  <li><p>Using "Microsoft SQL Server Management Studio" (version 10.0.1600.22) I have connected to the database, with blank username and password. I then have created a new database, called "Test1", and directed the created file, to reside at: "F:\Data\SQL Server\Test1.sdf".
  I continued with creating a couple of (trivial) tables, populated a couple of lines, and successfully ran a couple of queries - all using "Microsoft SQL Server Management Studio". So far so good.</p></li>
  <li><p>But, I need to interface to the database from an external program, using an ODBC connection. 
  So, using "ODBC Data Source Administrator" (\%sysdir%\SysWOW64\odbcad32.exe version 6.1.7600.16385) (64 bit), I am trying to create a data source interface. Here are my experiences: (I call the dsn: "SYS_DB"):</p></li>
  </ol>
  
  <p>3a. When going "System DNS" ->Add-> "SQL Native Client 10.0"; (for the driver); Server: PLUTO\SQLEXPRESS; "With integrated Windows authentication"; (all other options: default), when I press: "Test Data Source", it says: 
  <pre>... ... Address PLUTO\SQLEXPRESS' was used to establish the connection.
  ... TESTS COMPLETED SUCCESSFULLY!</pre>(PLUTO is the computer name).</p>
  
  <p>3b. The problem is, I need to connect to the "Test1" database, not to the default "master" database. </p>
  
  <p>So, in the third window in the "ODBC Data Source Administrator" window, when I go: ->Change the default database to: Test1, I get a window saying: <pre>The database entered is not valid</pre> If I go one window back, and uncheck: "Connect to SQL Server to obtain setting for the additional configuration options", and then I check: "Attach Database Filename": F:\Data\SQL Server\Test1.sdf (like I mentioned in 2. above), the datasource test fails with:
  <pre>Attempting connection
  [Microsoft][SQL Server Native Client 10.0][SQL Server] 
  The header for file 'F:\Data\SQL Server\Test1.sdf' is not a valid database file header.
  The FILE SIZE property is incorrect.</pre>3c. When trying to set the driver: "System DNS" ->Add-> "SQL Server";  (instead of "SQL Native Client 10.0" as in 3a. above), the behavior is identical: success when connecting to the default "master" database, and failure when trying to connect to "Test1" database, with the same error message as in 3b. above.</p>
  
  <ol>
  <li>Note: when I go: "SQLcmd -L", it says:<pre>Servers:
  PLUTO
  PLUTO\SQLEXPRESS</pre>I've googled throughout the Net and haven't found a solution. Your help will be appreciated.</li>
  </ol>
  
  <p>Many TIA - Helen</p>
  
  <p>Further information (3-16): when I go:<pre>>sqlcmd -S pluto\sqlexpress -Q "select getdate()"
  2013-03-16 10:07:04.380 (1 rows affected)</pre>so connection with the default ("master") database works fine. </p>
  
  <p>But when I go: </p>
  
  <pre><code>&gt;sqlcmd -S pluto\sqlexpress -d Test1 -Q "select getdate()"
  Msg 4060, Level 11, State 1, Server PLUTO\SQLEXPRESS, Line 1
  Cannot open database "Test1" requested by the login. The login failed.
  Msg 18456, Level 14, State 1, Server PLUTO\SQLEXPRESS, Line 1
  Login failed for user 'PLUTO\MyUser'.
  </code></pre>
  
  <p>So it seems that the problem is with user privileges/access rights to the new database on that server, namely: "Test1".</p>
  
  <p>Can anyone help with how to inspect/assign permissions to a specific database on SQL Server?</p>
  
  <p>Many TIA</p>
  
  <p>Helen</p>