MS SQL-Server: Cannot ODBC- Connect On Windows 7?

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, no

Solution 1:

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

Solution 2:

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