February 16, 2005 at 3:31 am
Dear security experts,
The normal domain accounts cannot select data from another SQL Server.
The servers are A and B under Windows 2003 and SQL Server 2000 SP3a.
The databaseses are A.db_A and B.db_B
The SQL Server service account is DOM\sqlsvc.
The user account is DOM\BenA and he is member of the role db_owner in both databases.
He tries unsuccesfully to select data from B.db_B when he is active in A.db_A
The error code is 18456 Severity Level 14 Line 1 Login failed for user 'DOM\BenA'
The setups are:
1. DOM\BenA is member of the db_owner database role in both databases
2. The servers were linked in Query Analyzer on both servers with:
A: exec sp_addlinkedserver 'B', N'SQL Server'
exec sp_addlinkedsrvlogin 'B','true'
B: exec sp_addlinkedserver 'A', N'SQL Server'
exec sp_addlinkedsrvlogin 'A','true'
3. The service account for SQL Server DOM\sqlsvc is "trusted for delegation".
4. Both servers are "trusted for delegation".
5. The user, that request delegation, is not sensitive and can be delegated.
6. A Service Principal Name (SPN) was assigned to the service account for the server B:
setspn -A MSSQLSERVER/A.dom.de:1433 DOM\sqlsvc
All these reccomendations were found in SQL Server Books Online.
Thank you in advance for the right tips to solve this delegation problem
Andreas Marner
February 17, 2005 at 4:57 am
A couple things to check/ ensure.
1. DOM\BenA is also trusted for delegation?
2. You are connected to the database servers in QA using NTLM?
I have delegation functioning using one db server in a web app where the db request follows this path: ClientPC --> IIS box --> DB Server. I did not do a setspn operation where the db server was the target.
Mike
February 17, 2005 at 5:16 am
This may help.
Kerberos config
http://support.microsoft.com/default.aspx?scid=kb;en-us;319723
Kerberos troubleshooting:
http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx
Good luck
Mike
February 19, 2005 at 12:35 pm
Kerberos works on TCP/IP, so make sure that in protocols order (connection utilities) first protocol is TCP/IP and second - Named Pipes. When I tested accounts delegation I could not connect until removed Named Pipes from connection utilities.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply