Sql-server – Permissions required for an SQL Server account

From My understanding of this issue it's a "HOP" issue.

i.e. you are trying to use server A to relay your login details (with SSPI) to Server B.

In SQL Server 2005 they have added a whole load of security issues that make this harder than it should be. The words "Kerberos Authentication" will become the bain of most sys-admins/DBA's lives. It effectively is used for pass-through authentication.

Here are the basics of what you need. 1) The servers (A and B) need to be set-up in Active Directory(AD) with delegation for Kerberos enabled. (this is set through your active directory admin panel)

2) The service account that your SQL Servers run under need to have delegation enabled also (this is also set through your active directory admin panel). - if they are not running under a service account, you need to create one.

3) The Servers need to have SPN's defined for the instance and the HOST and the machine name. (Using a tool called SetSPN in the windows support tools)

Support Tools (SetSPN is in this set) http://www.microsoft.com/downloads/details.aspx?FamilyID=96a35011-fd83-419d-939b-9a772ea2df90&DisplayLang=en

(Overview of how to add an SPN) http://technet.microsoft.com/en-us/library/bb735885.aspx

4) You may need to set your DB to "trustworthy"

ALTER DATABASE SET trustworthy on

5) After you have all of this done restart your instances.

6) Then try create your linked server again.

Finally you can test your connection to SQL Server. This should work fine if you have it all configured correctly.

SELECT *
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=ServerB;Integrated Security=SSPI;'
    ).MASTER.dbo.syscolumns

This will tell you your connection authentication type.

select auth_scheme from sys.dm_exec_connections where session_id=@@SPID

You want to get 'KERBEROS' here and not 'NTLM'.

It's a slippy slope, KERBEROS and Pass-through delegation, stick with it and you will eventually figure it out.

References Kerberos http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx

http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

http://blogs.iis.net/brian-murphy-booth/archive/2007/03/09/the-biggest-mistake-serviceprincipalname-s.aspx

Other manifestations of the problem http://www.sqlservercentral.com/Forums/Topic460425-359-1.aspx

http://msdn2.microsoft.com/en-us/library/aa905162(sql.80).aspx

http://msdn2.microsoft.com/en-us/library/ms189580.aspx

I hope this all helps.

This is what I did:

1. Create a new domain user named sql_user, granting logon to the SQL Server target machine
2. On the target SQL Server machine, using the users control panel add the domain user to the group SQLServer2005MSSQLUser$HOST$MSSQLSERVER (beware there is a similar group named: SQLServer2005MSFTEUser$HOST$MSSQLSERVER which it seems the user does not need to be a member of)
3. Grant sql_user the ability to lock pages in memory if using the AWE extensions, see also: MSDN article
4. Change the SQL Server service to startup as the new domain user in the service control panel
5. Give sql_user write permission to the c:\program files\microsoft sql server directory and all subdirectories
6. Give sql_user write permission to the datafiles for all of your databases
7. Restart the database service
8. Check the Windows Error log for any errors