July 15, 2005 at 7:56 am
I am getting the error: "Error 18452: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."
Scenario: Trying add Server B as a linked server to Server A.
Server A - Windows Server 2003 SP1, SQL Server 2000 SP4
Server B - Windows Server 2003 SP1, SQL Server 2000 SP4
The SQL Server and SQL Server Agent run as a domain user ad\database on both Server A and Server B.
The Client Network Utility lists protocols in the following order:
TCP/IP, Names Pipes
The Server Network Utility lists protocols in the following order:
Named Pipes, TCP/IP
In the security tab for properties of the linked server, the Local Login is set to ad\database with Impersonate checked off. No matter which radio button is set, the same error occurs ( except if I choose "Be made using this security context")
C:\Documents and Settings\xxxx>setspn -A MSSQLSvc/servera.test.com:1433
database
Registering ServicePrincipalNames for CN=database,OU=Service Accounts,DC=ad,DC=test,DC=com
MSSQLSvc/servera.test.com:1433
Updated object
C:\Documents and Settings\xxxx>setspn -A MSSQLSvc/serverb.test.com:1433
database
Registering ServicePrincipalNames for CN=database,OU=Service Accounts,DC=ad,DC=test,DC=com
MSSQLSvc/serverb.test.com:1433
Updated object
C:\Documents and Settings\xxxx>setspn -L database
Registered ServicePrincipalNames for CN=database,OU=Service Accounts,DC=ad,DC=test,DC=com:
MSSQLSvc/serverb.test.com:1433
MSSQLSvc/servera.test.com:1433
C:\Documents and Settings\xxxx>setspn -L ad\database
Registered ServicePrincipalNames for CN=database,OU=Service Accounts,DC=ad,DC=test,DC=com:
MSSQLSvc/serverb.test.com:1433
MSSQLSvc/servera.test.com:1433
I have also checked that the netbios names match using nbtstat.
In the AD in the "Delegation" tab, the radio button for "Trust this computer for delegation to any service (Kerberos only)" is selected for
both Server A and Server B.
In the AD in the "Delegation" tab for the ad\database, the "Trust this user for delegation to any service (Kerveros only)" is selected.
The checkbox "Account is sensitive and cannot be delegated" is unchecked.
I have also looked at serverb with SQL Profiler, and this is the information it gives:
EventClass: Audit Login Failed
TextData: Login failed for user '(null)". Reason: Not associated with a trusted SQL Server connection.
ApplicationName: Microsoft SQL Server
NTUserName: ANONYMOUS LOGON
LoginName: <is blank>
To get this error, I start a command prompt as the ad\database user, and then run the sql query analyzer and run the following:
select * from OPENQUERY("serverb",'select * from publogs.sysusers')
and the error comes up.
The user ( ad\database ) has public and dbowner privs on the remote table.
For reference from Microsoft, I have followed these 2 pages:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_4uuq.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp
If I enabled mixed mode on Server B, and map remote logins to the sa account, everything works fine. However, the whole point is to get this working with Windows Authentication only. ( please keep this in mind when replying )
Please let me know if you need any more information.
Thanks for any help / suggestions / solutions!
July 15, 2005 at 8:41 am
Some thoughts:
I've used this setup before, 30 odd servers all cross-connecting using passthrough auth.
1. Server A becomes a client of Server B thus should connect using tcp. kerberos will not work over np.
2. Rather than using the GUI for yourt linked server (which I think is rubbish) use this:
EXEC dbo.sp_addlinkedserver 'SERVERB', 'sql server'
--if needed
--EXEC dbo.sp_setnetname 'SERVERB', 'hostname'
--remove default login
EXEC dbo.sp_droplinkedsrvlogin 'SERVERB', NULL
--explicitly se^t up pass through mapping
EXEC dbo.sp_addlinkedsrvlogin 'SERVERB', 'true'
3. Try "setspn -A MSSQLSvc/serverb.test.com:1433 ad\database"
Your setspn calls don't specify the qualified user account even though I see your setspn -L call looks OK
4. Are you running setspn under an account that is domian admin or at least "Account Operator"? I've been bitten by that before.
5. The other chance is that the service account can logon locally but cannot logon over the network. Check the target server NT security log. You can test this by logging onto a client (ie your own PC ) as the service account and trying to connect via QA etc). I've also seen this.
Phew!
Hope these help
Cheers, Shawn
July 26, 2005 at 8:45 am
In SQL Client Network Utility, set Named Pipes at the top of the Enabled Protocols order.
If you dont need TCPIP, disable it. Our clients are all on an internal network, using BDE Admin, and IP will always kick out the "user 'null'" thing. I just disable TCPIP at the server and clients, leaving Named Pipes as the only enabled protocol and it all good.
It is a bit of a "work around" rather than a quality admin solution as suggested above, but it works for networks not needing IP. and so far the only thing that i've been able to make work out there for those I support.
~Clint
July 26, 2005 at 8:56 am
You need TCP/IP for kerberos and pass through to work. Period.
Personally, and I say personally, I'd disable named pipes.
Unless you have legacy apps (such as those using BDE admin or DB-LIB) it isn't needed.
The only apps that take a sulk with tcp is these legacy ones.
There was an issue with SQLOLEDB in .NET giving the same error using trusted over tcp (for various assorted reasons, according to google searches) but I (as DBA) suggested that the developers use "np:SERVERNAME" to force a protocol change rather than using an ODBC alias or client change.
As it happens, upgarding our AD to Win 2003 fixed this. I think. Anyway, the problem went away by some upgrade or patch applied somewhere. A positive undocumented feature!
Cheers, Shawn
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply