Sql-server – Linked Server – login trying to access database that is not default

linked-serversql serversql-server-2008-r2

Trying to create a linked server from server A (2005) to B (2008 R2). Everything worked when created. The login on server B was mapped to two databases: DataBase1 and Database2. Database1 was the default.

Database2 was taken off-line. When opening the User Mapping, there is a message, "One or more databases are inaccessible and will not be displayed in list." I assume Database2 is the culprit. I brought Database2 online and unchecked it.

When testing the connection on the Linked Server, it fails because it can't access Database2. Again, Database1 is the default.

I rebuilt the login using the exact same script with no mention of Database2, still get the error.

Because I need a different Linked Server name that does not match the actual SQL Server name, let's say I want to link to server "A" but call the link "X"

EXEC master.dbo.sp_addlinkedserver @server = N'X'
, @srvproduct=N'Microsoft OLE DB Provider for ODBC'
, @provider=N'MSDASQL', @datasrc=N'X'
, @provstr=N'DRIVER={SQL Server};SERVER=B;UID=test;PWD=*******;'

I can go into SSMS and create a linked server through the GUI. Picking SQL Server, which won't allow me to use a name for the linked server except the same name as the server and it works. This does not use the OLE DB Provider for ODBC.

EXEC master.dbo.sp_addlinkedserver @server = N'B', @srvproduct=N'SQL Server'

Can I script this so I can indicate the link name as X and the server or datasrc?

Best Answer

I would also change the parameters as follows:

  • @srvproduct = 'sql_server'

  • @provider = 'SQLNCLI' if you're coming from 2005 (and 'SQLNCLI10' from 2008/8R2, and 'SQLNCLI11' from 2012, etc.)

  • @provstr can be removed entirely; it's not necessary with this product and provider; your linked server logins will handle that.

Whatever SQL Server login is being used by the linked server probably has an offline/removed default database. Change the default database for that login to one that exists and that the user has permissions to and try again.

This is a guess, as you didn't post the CREATE USER script that generated the particular username logging in.

Related Question