MS SQL-Server: cannot ODBC- connect on Windows 7?

Question

I have a problem creating an ODBC data source connection for SQL-Server on Windows 7.

Problem summary: ODBC datasource connection can be created for the "master" database only, not for an additional, arbitrary database.

I have googled around the Net and haven't found a solution. Here are the details:

1. 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.

Using "SQL Server Configuration Manager", it says that:

SQL Server(SQLEXPRESS) running

I have also set up: "Log on as: built-in account -> Local System".

1. 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.

2. 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"):

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:

... ... Address PLUTO\SQLEXPRESS' was used to establish the connection.
... TESTS COMPLETED SUCCESSFULLY!

(PLUTO is the computer name).

3b. The problem is, I need to connect to the "Test1" database, not to the default "master" database.

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:

The database entered is not valid

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:

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.

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.

1. Note: when I go: "SQLcmd -L", it says:

   Servers:
   PLUTO
   PLUTO\SQLEXPRESS

   I've googled throughout the Net and haven't found a solution. Your help will be appreciated.

Many TIA - Helen

Further information (3-16): when I go:

>sqlcmd -S pluto\sqlexpress -Q "select getdate()"
2013-03-16 10:07:04.380 (1 rows affected)

so connection with the default ("master") database works fine.

But when I go:

>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'.

So it seems that the problem is with user privileges/access rights to the new database on that server, namely: "Test1".

Can anyone help with how to inspect/assign permissions to a specific database on SQL Server?

Many TIA

Helen

Answer 1

It turns out that the "Test1" database was in the "compact" instance. (That could have been noticed seeing the "sdf" database file extension in my OP).

So this was the reason the ODBC connection did not see it. (SQL Server Compact edition does not have an ODBC pipe).

Answer 2

you are running on permission problems hope this helps.

sp_addlinkedsrvlogin

Creates or updates a mapping between logins on the local instance of Microsoft® SQL Server™ and remote logins on the linked server. Syntax

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' [ , [ @useself = ] 'useself' ] [ , [ @locallogin = ] 'locallogin' ] [ , [ @rmtuser = ] 'rmtuser' ] [ , [ @rmtpassword = ] 'rmtpassword' ] Arguments

[@rmtsrvname =] 'rmtsrvname'

Is the name of a linked server that the login mapping applies to. rmtsrvname is sysname, with no default.

[@useself =] 'useself'

Determines the name of the login used to connect to the remote server. useself is varchar(8), with a default of TRUE. A value of true specifies that SQL Server authenticated logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. false specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server. true for useself is invalid for a Windows NT authenticated login unless the Microsoft Windows NT® environment supports security account delegation and the provider supports Windows Authentication (in which case creating a mapping with a value of true is no longer required but still valid).

[@locallogin =] 'locallogin'

Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows NT user. The Windows NT user must have been granted access to SQL Server either directly, or through its membership in a Windows NT group granted access.

[@rmtuser =] 'rmtuser'

Is the username used to connect to rmtsrvname when useself is false. rmtuser is sysname, with a default of NULL.

[@rmtpassword =] 'rmtpassword'

Is the password associated with rmtuser. rmtpassword is sysname, with a default of NULL. Return Code Values

0 (success) or 1 (failure)

Remarks

When a user logs on to the local server and executes a distributed query that accesses a table on the linked server, the local server must log on to the linked server on behalf of the user to access that table. Use sp_addlinkedsrvlogin to specify the login credentials that the local server uses to log on to the linked server.

A default mapping between all logins on the local server and remote logins on the linked server is automatically created by executing sp_addlinkedserver. The default mapping states that SQL Server uses the local login's user credentials when connecting to the linked server on behalf of the login (equivalent to executing sp_addlinkedsrvlogin with @useself set to true for the linked server). Use sp_addlinkedsrvlogin only to change the default mapping or to add new mappings for specific local logins. To delete the default mapping or any other mapping, use sp_droplinkedsrvlogin.

Rather than having to use sp_addlinkedsrvlogin to create a predetermined login mapping, SQL Server can automatically use the Windows NT security credentials (Windows NT username and password) of a user issuing the query to connect to a linked server when all these conditions exist:

• A user is connected to SQL Server using Windows Authentication Mode.
• Security account delegation is available on the client and sending server.
• The provider supports Windows Authentication Mode (for example, SQL Server running on Windows NT).

After the authentication has been performed by the linked server using the mappings defined by executing sp_addlinkedsrvlogin on the local SQL Server, the permissions on individual objects in the remote database are determined by the linked server, not the local server.

sp_addlinkedsrvlogin cannot be executed from within a user-defined transaction. Permissions

Only members of the sysadmin and securityadmin fixed server roles can execute sp_addlinkedsrvlogin.

Troubleshooting

source