Linked servers and Windows Authentication
I have seen similar questions, but none of the answers have solved my problem.
I have several SQL2000 servers running under Win2003. In the past, we have been using SQL logins, but I have been trying to transition to using Windows Authentication. It has been working great except that sometimes queries using linked servers fail.
The linked servers are set up for the connection to use the login's current security context.
For testing this problem, I have limited myself to two servers to do this between. My Windows account is defined with sa rights on both servers. I have tried explicitly to impersonate my account, I have told it to explicitly use the same account and password on the other server. I have tried not listing my account and letting it default my original setting of using current security context. (if I tell it to use the sa account remotely, that does work)
Both SQL Servers run under the same domain account. That account had delegation rights. My account is not marked as sensitive. The computer account has delegation rights. There is a Service Principle Name defined for both servers.
I have tried everything I can find to try, yet I still get the following message (or something similar, depending on which settings I have changed around).
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
What am I missing?
[1539 byte] By [
Dwatney] at [2007-12-19]
Can you check MDAC level between those 2 servers?
And check the impersonation settings again.
Both servers appear to have MDAC 2.80.1022 installed.
Both servers have a link to the other with no specific users listed and the default set to using the login's current security context.
Dwatney,
Did you ever get an answer to your question? I am having the exact same problem.
RoyAF,
Not yet, but I'm still moving people over to Windows Authentication hoping that an answer can be found before people show up at my door with tar and feathers, :-).
Have you been able to resolve this issue yet? I am having similar problems and haven't been able to get this to work. Seems my linked server (to sql server 2000) works intermittently.
Michael
My problem was self-inflicted. I was positive I had a login on the remote server but did not. When I created a login all worked well.
Another thing I discovered when I was trying to set up another user, to access the remote server, was that the login alreay exists. After a lot of head scratching, I discovered that the database had been copied from another server and attached to this database. And, they did not move and attach master thus leaving a lot of orhpaned logins, I deleted all of the orhphans from sysusers and all is working.
Roy
Michael,
I will try cleaning up orphans, as suggested above, but otherwise, I am no closer to an answer. There was the question about the version of MDAC. I haven't checked to see if that version translates to the original version 2.8 or one of the later serice packs. If the original, I could try updating to a later service pack.
Try a little more detail on your situation. Pick one configuration, detail it and post the error.
For example;
Specify “Be made using the login's current security context” in the properties of the linked server.Look at the Logins is 'yourdomain\yourusergroup' listed and granted login?
Are you authenticated to Server1 through the 'yourdomain\yourusergroup' group when you run a distributed query to Server2?
Which queries fail, and which queries succeed?
What error appears in the application event log on server2 when a query fails?
Server1 is Windows 2003 Enterprise, SQL Server 2000 Enterprise SP4.
Server 2 is Windows 2003 Standard, SQL Server 2000 Standard SP4.
Both servers are in the same domain. Both servers run their sql service as the same domain user. That domain user has been given the "Account is trusted for delegation" option in Active Directory.
Each server had a linked server connection to the other. The security settings are such that every connection will "be made using the login's current security context." (In the linked server security tab, there are no login mappings and the option for what to do if not in that list is that connections will "be made using the login's current security context.")
On each server, my domain account is defined as a login with sa rights. Normally, it would be done as part of a group, but to simply matters, I made my individual account a login. If it matters, my domain account happens to be a domain admin, but obviously that won't be the case for others.
On Server1, I run the following query: "Select * From Server2.mydatabase.dbo.mytable" and I get
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
I see no errors in the Event Viewer Application log. I see some successful audits in the Security log for my account (and no failures).
At the moment, I don't have an example of a query that actually works.
I am having a similar issue with SQL 7.0 and linked servers... but I get the following error:
Login Failed for user 'NT Authority\ANONYMOUS LOGON'...
If anyone as any suggestions, please let us know..
Thanks!
ggentile,
That is an error message I have seen, too, while working with this problem.
I had the same problem and posted my solution.
I would strongly recommend that you check to be sure you have a login on the remote computer. I was positive I did but, when I checked I discovered I did not.
HTH,
Roy
To set up a remote server to allow the use of remote stored procedures
-
Run the following code on the first server running Microsoft? SQL Server?:
2. EXEC sp_addlinkedserver ServerName1, N'SQL Server'
3. EXEC sp_addlinkedserver ServerName2
4. EXEC sp_configure 'remote access', 1
5. RECONFIGURE
6. GO
- Stop and restart the first SQL Server.
- Run the following code on the second SQL Server. Make sure you are logging in using SQL Server Authentication.
9. -- The example shows how to set up access for a login 'sa'
10. --from ServerName1 on ServerName2.
11. EXEC sp_addlinkedserver ServerName2, local
12. EXEC sp_addlinkedserver ServerName1
13. EXEC sp_configure 'remote access', 1
14. RECONFIGURE
15. GO
16. -- Assumes that the login 'sa' in ServerName2 and ServerName1
17. --have the same password.
18. EXEC sp_addremotelogin ServerName1, sa, sa
19. GO
- Stop and restart the second SQL Server.
- Using the sa login, you can now execute a stored procedure on the second SQL Server from the first SQL Server.
This is out the SQL books.. I did find that you will probably have to drop the servers first, to clear any entries…
Exec Sp_dropserver ‘RemoteServerName’, droplogins
You will not need step 18, by default, it should set the mapping to ‘Map all remote logins to - <Same Name>’.. you can check this in remote-servers in Enterprise Manager.
If you run sp_helpremotelogin you should see something like this.
Serverlocal_user_nameremote_user_nameoptions
‘localservername’** use local name **** mapped locally **
‘remoteservername’** use local name **** mapped locally **
RPC only works for local SQL account not NT. Can you logon to Query Analyzer as ‘sa’ and test the you can run a Stored Procedure on the other server. Create something stupid that will email ‘hello’, so you know it worked.
Once this works run the following:
Exec sp_serveroption @server = 'remoteserver’ ,@optname = 'data access',@optvalue = ‘True’
Check the server settings..
Exec Sp_helpserver
Namenetwork_namestatusid
‘remoteservername’‘remoteservername’rpc,rpc out,data access2
You should be able to run a distributed query now, with local or NT Authentication.
You will see entries in the Remote-Sever, but not in the Linked-Servers in Enterprise Manger.. at least I do not..
Good luck!
It turns out that my problem isn't as widespread as I thought. I started trying other pairs of servers and most of them work properly. One of the two I was using for my original testing seems to be the problem child of the whole bunch. I hope that by comparing it to a server which does all this just fine will reveal the problem.
