March 8, 2012 at 9:44 am
Any help is greatly appreciated. I have 2 SQL servers on my domain, one is 2005 and the other is 2008. I am trying to get the double-hop authentication to work (as referenced here: http://www.sqlservercentral.com/Forums/Topic269848-5-2.aspx#bm1080735) and just cannot seem to get it to work:
SQL management studio -> connect to server A -> run a query that used a linked server to server B (the double hop).
I have done everything listed here: http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx but still doesn't work.
One thing I read was that it is best practice to run SQL service as a domain account with minimal privledges, rather than as the 'Local System' account, so I wanted to see if that made a difference.
However, I cannot seem to find anything that outlines what the minimum domain or local permissions this account needs to run SQL without breaking anything.
Any help on how to set up this domain account to run SQL is greatly appreciated, thanks!
March 8, 2012 at 10:25 am
script out the linked server and post here...
March 8, 2012 at 10:28 am
Post the error you are receiving. Probably has to do with the linked server configuration or the permissions on the remote server. Also, assigning a domain user to a service using SQL Configuration Manager should take care of all of the necessary permissions for that domain user. DO NOT change service logins through anything other than the configuration manager.
Jared
CE - Microsoft
March 8, 2012 at 10:35 am
to answer your question though, most of the local rights required are assigned when the service account is specified (hence always use SSConifuration manager to change a service account). Some additional features require additional rights... see Reviewing Additional Considerations here: http://msdn.microsoft.com/en-us/library/ms143504.aspx
From a domain perspective, the only tricky item is the permission required to write an SPN to use Kerberos authentication. this explains those rights: http://technet.microsoft.com/en-us/library/cc773257(v=WS.10).aspx#BKMK_Delegating
March 8, 2012 at 11:09 am
Thanks again, I really appreciate the help.
The linked server is on Server A (SQL2005):
Server Type: Other Data Source
Name: Linkedserver
Provider: SQL Native Client
Data Source: Server\SQL2008
Provider string: Integrated Security=SSPI
Security is set to: "Be Made using the login's current security context"
I used this script to create it:
EXEC sp_addlinkedserver @server='Linkedserver',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='Server\SQL2008',
@provstr='Integrated Security=SSPI;'
Now if I log on to ServerA (Sql2005), and run this query, it works:
select * from Linkedserver.master.dbo.sysdatabases
But if I connect from my desk (SQL Server Management studio) and connect to ServerA as myself (domain\username) and run the same query, I get the following error:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
OLE DB provider "SQLNCLI" for linked server "Linkedserver" returned message "Invalid connection string attribute".
I have permissions for myself through AD group permissions to both datanbases.
any help is greatly appreciated, thanks!!
March 8, 2012 at 11:35 am
Go to the server objects\linked Servers and right-click on this object and select properties. Then click save or ok (I forget which one it is) to close it. My bet is that it will tell you that it has created the linked server, but cannot establish a connection. You probably have to specify a login in the security tab of the linked server properties.
Jared
CE - Microsoft
March 8, 2012 at 11:47 am
ericb1 (3/8/2012)
Thanks again, I really appreciate the help.The linked server is on Server A (SQL2005):
Server Type: Other Data Source
Name: Linkedserver
Provider: SQL Native Client
Data Source: Server\SQL2008
Provider string: Integrated Security=SSPI
Security is set to: "Be Made using the login's current security context"
I used this script to create it:
EXEC sp_addlinkedserver @server='Linkedserver',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='Server\SQL2008',
@provstr='Integrated Security=SSPI;'
Now if I log on to ServerA (Sql2005), and run this query, it works:
select * from Linkedserver.master.dbo.sysdatabases
But if I connect from my desk (SQL Server Management studio) and connect to ServerA as myself (domain\username) and run the same query, I get the following error:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
OLE DB provider "SQLNCLI" for linked server "Linkedserver" returned message "Invalid connection string attribute".
I have permissions for myself through AD group permissions to both datanbases.
any help is greatly appreciated, thanks!!
This isn't going to work this way with the service account set to local service. You were correct in your original post- you need to either (1) use a domain account for the SQL Server service or (2) set the linked server to connect using another account. One way to accomplish that is to change from "Be Made using the login's current security context" to specify an account, like a SQL Login.
March 8, 2012 at 11:52 am
Thanks. I've tried that as well, and it didn't work either. If I put in my login or use "domain\Administrator" (which is a valid login on both servers) and set it to impersonate it will give the error:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
If I take off the impersonate and specify the remote user of 'domain\Administrator', it gives me an error "Access to the remote server is denied because no login-mapping exists"
thanks again for your help!
March 8, 2012 at 11:59 am
ericb1 (3/8/2012)
Thanks. I've tried that as well, and it didn't work either. If I put in my login or use "domain\Administrator" (which is a valid login on both servers) and set it to impersonate it will give the error:Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
If I take off the impersonate and specify the remote user of 'domain\Administrator', it gives me an error "Access to the remote server is denied because no login-mapping exists"
thanks again for your help!
Don't impersonate... Use "Be made using this security context" with an SQL Server login (not a domain login). Let us know if that works.
Jared
CE - Microsoft
March 8, 2012 at 12:31 pm
yes, when I use a regular SQL login on the remote server as the security context, it works.
But correct me if I'm wrong, that is an "always on" connection that anyone can use with all the permissions assigned to that login? For security sake, I would not like to have that there.
Since I have an AD group permission login on ServerA, and the same group(s) on serverB, I would like to restrict the use of the linked server so that it verifies the user on serverA (in my case it is a team of developers) and only if they have the permissions on the linked server, will their query or package work correctly.
At least that's how I understand "be made using the login's current security context" option to work.
So if I use the option "be made using this security context:" and supply a SQL login (even with read only access) to the remote linked server for a database on that linked server that contains say HR salary information (for my own use), and I have given access to serverA to a jr. develper to run reports on sales data, that developer can use the linked server to access salary data on the remote/linked server even though he is not supposed to or has no direct access to it?
That's what I have found, and so I wanted to be able to have the linked server, but have it use your domain credentials to access the data on the linked server, and deny access if you don't have the necessary permissions on serverB
thanks again!
March 8, 2012 at 12:42 pm
If you want every user to pass their domain credntials, you need to change the sevice acct. If you have a small group of users who need to use the linked sever, you can specify them in the list at the top of the linked server security config ans have them impersonate a sql login, then set the bottom to be "no be made" for users not in the list. That will prevent your junior folks from using the linked server since only the logins in the list will use impersdonation to pass a sql login
March 8, 2012 at 12:43 pm
If you want every user to pass their domain credntials, you need to change the sevice acct. If you have a small group of users who need to use the linked sever, you can specify them in the list at the top of the linked server security config ans have them impersonate a sql login, then set the bottom to be "no be made" for users not in the list. That will prevent your junior folks from using the linked server since only the logins in the list will use impersdonation to pass a sql login
March 8, 2012 at 12:44 pm
Ok, well the good news is that it works. So the remote server is allowing the connection and the data properties are set correctly. I can't help too much more as we don't use linked servers in this way. We would set permissions on the first server so that they could not do the same on the remote.
Jared
CE - Microsoft
March 8, 2012 at 12:47 pm
Have you read this article? http://www.sqlservercentral.com/articles/Security/65169/
Especially the part about setting SPNs for each service.
I don't know if it has changed in recent version of windows but you used to have to give the service account local permissions to 'impersonate' a login for the multiple hops to work.
March 8, 2012 at 1:54 pm
check your SPNs and enable delegation on the computer account
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply