Unable to Create Linked Server After Domain Migration

Question

We have a server (ServerTarget) that we recently migrated from domain1.company1.com to domain2.company2.com. The server contains both a SQL Server 2K instance ("ServerTarget") and a SQL Server 2K5 instance ("ServerTarget\SQL2K5").

We have another server ("ServerDB") running SQL Server 2K that accesses data on both of those instances using TSQL linked servers. ServerDB is on domain2.company2.com and uses SQL Authentication to connect to ServerTarget.

Post-migration, the existing TSQL linked server code was no longer creating usable linked servers for either instance ("Server does not exist" errors). By adding the domain suffix to the code for the 2K instance (Server=ServerTarget.domain2.company2.com), we were able to get it working again. However, that "fix" doesn't work on the 2K5 instance (Server=[ServerTarget\SQL2K5].domain2.company2.com).

We currently CANNOT create a usable linked server to the 2K5 instance from SS Mgmt Studio or Enterprise Manager either. (Error 7399, "SQL Server Does Not Exist or Access Denied".) However, we CAN connect to the server (using the SQL login/password) from SS Mgmt Studio, and access the target database (which is also that user ID's default database).

1) Mixed Mode Authentication is enabled on ServerTarget\SQL2K5.

2) Allow Remote Connections is enabled on ServerTarget\SQL2K5.

3) Windows Firewall is off on ServerTarget.

I'm out of ideas on this one. Help!

Existing TSQL Code:

EXEC sp_addlinkedserver
@server = 'ServerTarget_Conn'
, @srvproduct = ''
, @provider = 'MSDASQL'
, @provstr = 'DRIVER=SQL Server;SERVER=[ServerTarget\SQL2K5];UID=UserID;PWD=Password;'

Full Error Message Received for All Queries:

[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:   ].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.  

Answer 1

To start, check the error log on the target (I think that is [ServerTarget\SQL2K5].domain2.company2.com). At the moment you have two possible error messages: Doesn't exist (meaning the target can't be found) or access denied (meaning the server was found but the login didn't work). The error log on the target will either show nothing - indicating the target couldn't be found, or if the problem is access denied you should find a better error message about what happened.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 2

Rick, thanks for taking the time to respond.

Attempts to query the linked server are not generating a message in the SQL Server Log or in any of the Windows Event logs. So it would appear that attempts are not locating the target server.