December 9, 2011 at 6:41 am
I'm the same way... really good at database stuff... really bad with Windows and Network Security. So let me ask the question... will this also solve the similar problem that occurs when using things like BULK INSERT even though I've created the appropriate "Share"?
<DESKTOP> - HOP - <SQL SERVER> - HOP - <Some Server On The Domain With a File On It To Be BULK INSERTED>
--Jeff Moden
Change is inevitable... Change for the better is not.
December 9, 2011 at 7:03 am
Very timely since this topic came up just this week - thanks!
December 9, 2011 at 7:05 am
Greg Edwards-268690 (12/9/2011)
...Good job giving a short overview. Many give up trying to set this up, and resort to workarounds.
I'm a DBA on some development and QA servers but not in production, so I have to request permission before I develope a database or job that leverages a linked server. Considering the issues involved, it's no wonder why many DBAs punt and configure the linked server to use SQL Server account authentication, or they just disallow the use of linked servers all together. This article is succinct and could be very helpful.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 9, 2011 at 7:10 am
I'll just add this info about Kerb, in case someone's looking...
Your two servers must have their system times set closely...Within two mins, I believe. If not, you will get the infamous "Cannot generate SSPI context" message.
-Grubb
December 9, 2011 at 7:13 am
I think it should be noted that the "hop" problem pops up not just between instances of SQL Server...it can happen anywhere that credentials need to be delegated between "things". For instance, it can happen with a web application with a SQL Server back-end.
December 9, 2011 at 7:17 am
Jeff Moden (12/9/2011)
I'm the same way... really good at database stuff... really bad with Windows and Network Security. So let me ask the question... will this also solve the similar problem that occurs when using things like BULK INSERT even though I've created the appropriate "Share"?<DESKTOP> - HOP - <SQL SERVER> - HOP - <Some Server On The Domain With a File On It To Be BULK INSERTED>
It should work, even though I haven't tried it myself.
According to BOL for BULK INSERT:
If a SQL Server user is logged in using Windows Authentication, the user can read only the files accessible to the user account, independent of the security profile of the SQL Server process.
My understanding of this is that if you're logged in using Windows authentication then BULK INSERT will try to impersonate your account when accessing the files. All you need is to make sure that your connection to SQL Server is using Kerberos (column auth_scheme in sys.dm_exec_connections) and that the service account running SQL Server is allowed to delegate your account to the server hosting the file you want to BULK IMPORT.
December 9, 2011 at 7:48 am
Kenneth, an excellent article!Thanks,
I have the same question
"how would the implementation steps differ, if at all, were both ServerA and ServerB using the same SQL Server Service Account?".
Thanks,
December 9, 2011 at 8:46 am
Good Post. I have run into this many times. Actually this is true for most things using Kerberos. I had the same issues when using Kerberos and IIS web services in a load balanced environment.
December 9, 2011 at 8:58 am
Kenneth, thank you for your article. I've encountered this problem a few times in my environment and it's very annoying to say the least. In my situation the problem is compounded by the fact that, for security reasons, we've disabled kerberos entirely!
I tend to encounter this problem with web servers that are accessing remote databases. So SharePoint is one where this has been a problem when I want to allow a user to access some replicated data on the reporting server. SharePoint is slightly different than other web servers because of it's security model and it isn't just limited to SQL Server database users. We have some web developers try to publish lists coming from Oracle DBs on Solaris servers where the same problem is encountered.
With some web applications the problem can be resolved by granting 'NT Authority\Anonymous Logon' permissions to the database being accessed but this is a major security problem in my opinion. Another method I've found of getting around this problem is to grant the machine account the logon is coming from access to the resource (create login [domain\computer$] from windows with default_database=[dbName]).
Thank you again for your article, this is the best information I've found so far on this topic and it's somewhat comforting to know that there are others out there who have struggled with this problem too.
December 9, 2011 at 9:28 am
I'm in the process of implementing a solution so I'm not entirely confident I'm correct; however, I wanted to point out an issue with the SPN comment.
To my knowledge, the ability to write an SPN to the AD requires domain admin permissions and I'm fairly sure the AD admins will have a problem with this. My solution (still in progress) is to...
1. Specify a static IP port for the SQL instance(s).
2. Request creation of SPNs for servers involved in the double-hop connection.
3. Request the SQL service accounts be granted "trust for delegation" permissions.
If you change the IP port of the SQL instance you'll need to request the SPN be updated to match the new IP port.
December 9, 2011 at 9:33 am
bruce.l.pettus (12/9/2011)
I'm in the process of implementing a solution so I'm not entirely confident I'm correct; however, I wanted to point out an issue with the SPN comment.To my knowledge, the ability to write an SPN to the AD requires domain admin permissions and I'm fairly sure the AD admins will have a problem with this. My solution (still in progress) is to...
1. Specify a static IP port for the SQL instance(s).
2. Request creation of SPNs for servers involved in the double-hop connection.
3. Request the SQL service accounts be granted "trust for delegation" permissions.
If you change the IP port of the SQL instance you'll need to request the SPN be updated to match the new IP port.
You can certainly do it this way, and in fact as far as In understand it that is how you have to do it for SQL 2000. However with 2005 and above if you grant the service account Read/Write Service Principal Name then SQL can create/drop the SPNs as needed. That permission very definitely requires a domain admin to grant, but its a lot easier than having them create the SPNs manually, and fairly safe also since its the service account that has the permission, not a user. And of course since its your service account for SQL Server you should have it locked down fairly tightly to begin with.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
December 9, 2011 at 9:41 am
There hasn't been much explanation of why SPNs are important.
Given servers A and B in which the user has authenticated to server A with a domain account. When server A tries to establish the second hop it will send - among other things - an SPN to server B. Server B will attempt to validate the SPN with the AD. If the supplied SPN cannot be found in the AD you will get the "unable to generate SPN context" error. I believe this is a security mechanism to ensure server A is in fact a member of the domain - or at least is known to AD.
Someone noted that each instance must have its own service account. I'm not sure this is true; however, it is true that each server forwarding (second hopping) must have its own SPN in the AD.
December 9, 2011 at 9:42 am
chinn (12/9/2011)
Kenneth, an excellent article!Thanks,I have the same question
"how would the implementation steps differ, if at all, were both ServerA and ServerB using the same SQL Server Service Account?".
Thanks,
chinn, good question, you can see the Russell Fields'
answer on page 2. Honestly I didn't know myself 🙂
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
December 9, 2011 at 9:46 am
Another scenario you might get the dreaded cannot login as user 'null' is when you are trying to use windows authentication across trusted domains.
If the trust between the domains breaks down the AD server on the far side can't request information about your user account and will send this error. Once you restore the trust everything works fine.
We fought this problem for months and finally figured out that it is not a good idea to have your AD servers named the same even though they are on different domains. Go figure.
December 9, 2011 at 9:46 am
bruce.l.pettus (12/9/2011)
Someone noted that each instance must have its own service account. I'm not sure this is true; however, it is true that each server forwarding (second hopping) must have its own SPN in the AD.
Actually that was me, but it's been noted in this discussion that I was wrong :). The documentation I read said that it was best practice to have seperate service accounts for each instance, so I've never actually tried using a single service account for multiple instances.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 15 posts - 16 through 30 (of 58 total)
You must be logged in to reply to this topic. Login to reply