InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 01:13:52 GMT20http://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxI have a very intelligent friend who is a Server/systems/directory MVP.At the MVP summit he makes a comment to me that MSSQL is just like a spread sheet, to me and one of the SQL MVPs.We looked at him with disbelief. No WAY. My happenstance came from doing system and database work starting in the DBASE III Plus days and JCL/IBM QUERY on AS/400s. Getting the data processed and delivered seems like a logical pair like cooking the meal and serving it.So I have often wondered why it was separated? We have better tools then ever to do it. But I guess I'm just an odd one out. Thu, 29 Dec 2011 20:05:17 GMTosgcurthttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxIn my opinion one of the largest problems DBAs have with server level security (Kerberos, Service Account permissions etc) is that it is "Server Level", meaning it's outside of SQL Server. Most DBAs I've met over the years have no interest in learning anything that isn't directly part of SQL. If it can't be updated using the SQL Server toolset then they don't want to mess with it. That is something for the "server guy/team". Then on the other side, the server support people generally have no interest in learning (and probably with better justification) what the SQL Servers/Service Accounts need by way of security.Because of that you only have the rare DBA who started as a server admin, or who becomes a server admin that will actually understand both sides of the puzzle. This of course means that what documentation there is, is directed to the server admins, and is much harder to understand for us poor DBAs.When I went to the Pass Summit there was a session I had looked forward to seeing called "Windows Operating Systems Internals for Database Pros" by Brian Kelly. Unfortunately it was canceled. One of these days when I have some free time I'll have to see if he has it on line somewhere.Thu, 29 Dec 2011 18:00:05 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxYes,I've seen that enough as well. I see both sides of the security mirror. Often I've been in an IT department with very experienced folks and DBAs and they tell me they have never heard of delegation issues. I blame the "VENDOR" for not educating the professionals.Some of the deployment guides are very general and scanty. A friend of mine, Mark gives a great talk on Windows Logins which is way past the "logging into the machine" stage.He is way into the packet area of the subject.A few years back, we had many double hop Kerberos issues on SQL based application servers.His talks really helped me understand the issues.Seems I learn something new everyday from some one here or some place else.Thu, 29 Dec 2011 10:17:17 GMTosgcurthttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]Kenneth.Fisher (12/29/2011)[/b][hr][quote][b]ALZDBA (12/13/2011)[/b][hr]Just today I've seen a tweet pointing to a delegation troubleshooting blog:[url]http://blogs.msdn.com/b/sqlserverfaq/archive/2011/12/12/troubleshooting-kerberos-delegation-using-delegconfig.aspx[/url][/quote]Unless I'm missing something it looks more like an advertisement for something called DelegConfig. I don't now anything about the tool or its usefulness though.[/quote]Maybe you should take a closer look. I think you are missng something.Thu, 29 Dec 2011 09:22:18 GMTGreg Edwards-268690http://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]osgcurt (12/29/2011)[/b][hr]My problem as a consultant is finding an AD admin that even knows what an SPN is, much less understanding how they work with applications like MSCRM, Share Point and Business Portal.Sometimes I do not see an SPN for the default instance but there are SPNs for subsequent instances. It's the result of using accounts for the services that do not have the right to create the attributes in the LDAP database.[/quote]I would expect, as a consultant, you would be able to 'guide' any domain administrator with setting this up. That 'problem' might be part of why someone might use a consultant.Don't complain too loudly about this.:-DVery few accounts have the ability to write information to AD.And running a service under an account that can do this might open up some security holes.Not a best practice.NT Authority System comes to mind, along with SQL Injection.Thu, 29 Dec 2011 09:20:22 GMTGreg Edwards-268690http://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]ALZDBA (12/13/2011)[/b][hr]Just today I've seen a tweet pointing to a delegation troubleshooting blog:[url]http://blogs.msdn.com/b/sqlserverfaq/archive/2011/12/12/troubleshooting-kerberos-delegation-using-delegconfig.aspx[/url][/quote]Unless I'm missing something it looks more like an advertisement for something called DelegConfig. I don't now anything about the tool or its usefulness though.Thu, 29 Dec 2011 08:03:16 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxMy problem as a consultant is finding an AD admin that even knows what an SPN is, much less understanding how they work with applications like MSCRM, Share Point and Business Portal.Sometimes I do not see an SPN for the default instance but there are SPNs for subsequent instances. It's the result of using accounts for the services that do not have the right to create the attributes in the LDAP database.Thu, 29 Dec 2011 06:17:35 GMTosgcurthttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxHi Kenneth,Thanks for the in depth explanation. I was just about to setup an "poller" of sorts on a central SQL server that would have exactly this problem.Theo (NL)Wed, 28 Dec 2011 23:41:18 GMTTheo Ekelmanshttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxJust today I've seen a tweet pointing to a delegation troubleshooting blog:[url]http://blogs.msdn.com/b/sqlserverfaq/archive/2011/12/12/troubleshooting-kerberos-delegation-using-delegconfig.aspx[/url]Tue, 13 Dec 2011 00:00:32 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]Greg Edwards-268690 (12/9/2011)[/b][hr][quote][b]ALZDBA (12/9/2011)[/b][hr]Nice article. And what's most important .... It will help people diagnose and solve this kind of issues !With fixed ports, you can also register the spn yourself ( after install of the instance ) using this DOS script[code="plain"]echo offrem http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspxrem To create an SPN for SQL Server, enter the following code at a command prompt:rem setspn -A MSSQLSvc/Host:port serviceaccountrem For example:rem setspn -A MSSQLSvc/server1.redmond.microsoft.com sqlaccount echo onrem C:\Program Files\Support Tools\setspn -A MSSQLSvc/yourservername.yourdomain.yourdomainsuffix:2388 DOMAIN\SERVICEACCOUNT[/code][/quote]So you are a domain admin?[/quote]For the required permissions, have a look at :[url]http://technet.microsoft.com/en-us/library/cc773257(WS.10).aspx[/url]more details for domain admins to delegate this to a sqlserver sysadmin can be found at [url]http://technet.microsoft.com/en-us/library/cc773257(WS.10).aspx#BKMK_Delegating[/url]Being domain admin would make this easy, but chances are I would break more things by simply not knowing what AD or a domain holds, needs and how simple it is to mess it up, so ... no thanks.:hehe:Sun, 11 Dec 2011 11:32:36 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]SpringTownDBA (12/9/2011)[/b][hr]Hey Kenneth,Thanks for your reply. I apologize if my original post seemed harsh, I've fought some extremely frustrating battles with kerberos in the past and want others to avoid my pain and anguish.[/quote]Absolutely. I spent a year and a half figuring out my solution, and that was with the help of someone who had already managed it. The whole purpose of this was to give other DBAs a cookbook abroach to fixing the problem 90% of the time. And that is with the realization that a lot of DBAs are going to look at an in depth explanation of Kerberos and their eyes are going to glaze over right before the move on to the next article. I know I did several times.[quote]As for automatic spn registration, there is a recommendation against using automatic registration on a cluster: [url]http://blogs.msdn.com/b/psssql/archive/2010/03/09/what-spn-do-i-use-and-how-does-it-get-there.aspx[/url] (which I just found about 15 minutes ago, the rationale is in the comments)[/quote]I'll have to read this when I have some time. Clusters do tend to be an exception to the rule frequently.[quote]My original thoughts on manual being better are due to consistency with other Microsoft products that don't auto-register themselves (SSRS, SSAS, Sharepoint 2010 components, etc), and explicit registration forcing DBA's and Sys Admins to become more familiar with kerberos configuration. Unfortunately, the easy way to get spn's registered is to run sql as a domain admin account. The second easiest is to ask a system administrator to run "setspn.exe ...". The third is to grant the specific permissions to the service account (as you mentioned).[/quote]My office doesn't do much with SSRS or SSAS and I haven't gotten to play with our Sharepoint yet so I can't talk about those myself. Using the domain admin account is definably the easiest method. Of course so would eliminating all security. And believe me I've wished I could several times.;-) Asking the system administrator to create the SPN is only easy if they are willing to construct the command or if you know enough to construct it yourself. I'm getting closer on that myself, but I'm not still not confident in it. For me the easiest method was to ask for a specific permission. Of course I'm very glad that several people have mentioned the method for creating the SETSPN.exe command since I think that everyone should be able to make their own choice.[quote]As for constrained vs unconstrained,the current guidance from MS for Sharepoint configuration strongly pushes constrained delegation. Because constrained -> unconstrained doesn't work (and is very hard to trace as the culprit), I would only recommend configuring constrained delegation on your sql service accounts. I believe that the guidance from MS recommending unconstrained delegation for SqlServer hasn't been revisited in several years (could be wrong though)[/quote]Wouldn't surprise me in the slightest if they needed to update their documentation. I agree that constrained delegation is best. Of course at the time it threw me a bit, so I decided I should put in both options. [quote]If you wrote a follow-up article on configuring kerberos for SSRS and/or SSAS, that might be helpful as well. [/quote]If I ever have the time to play with it with SSRS and SSAS I'll give it a shot :-)[quote]I hate seeing people avoid kerberos because it's too hard to get configured or they don't know how to troubleshoot it. [/quote]Absolutely! And please understand I appreciate every response people have made to this article. I love responses that continue to help people further understand the problem and it's solutions, all of the solutions, not just the one I wrote about.Sat, 10 Dec 2011 19:08:04 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]SpringTownDBA (12/9/2011)[/b][hr]Hey Kenneth,Thanks for your reply. I apologize if my original post seemed harsh, I've fought some extremely frustrating battles with kerberos in the past and want others to avoid my pain and anguish.As for automatic spn registration, there is a recommendation against using automatic registration on a cluster: [url]http://blogs.msdn.com/b/psssql/archive/2010/03/09/what-spn-do-i-use-and-how-does-it-get-there.aspx[/url] (which I just found about 15 minutes ago, the rationale is in the comments)My original thoughts on manual being better are due to consistency with other Microsoft products that don't auto-register themselves (SSRS, SSAS, Sharepoint 2010 components, etc), and explicit registration forcing DBA's and Sys Admins to become more familiar with kerberos configuration. Unfortunately, the easy way to get spn's registered is to run sql as a domain admin account. The second easiest is to ask a system administrator to run "setspn.exe ...". The third is to grant the specific permissions to the service account (as you mentioned).As for constrained vs unconstrained,the current guidance from MS for Sharepoint configuration strongly pushes constrained delegation. Because constrained -> unconstrained doesn't work (and is very hard to trace as the culprit), I would only recommend configuring constrained delegation on your sql service accounts. I believe that the guidance from MS recommending unconstrained delegation for SqlServer hasn't been revisited in several years (could be wrong though)If you wrote a follow-up article on configuring kerberos for SSRS and/or SSAS, that might be helpful as well. I hate seeing people avoid kerberos because it's too hard to get configured or they don't know how to troubleshoot it. thanks![/quote]I prefer ADSI Edit to enter SPN's to SETSPN.exe.Learning to setup Kerberos properly and troubleshoot issues is well worth learning.It allows user level security based on the users credentials.We also use SSAS, and it is pretty much a requirement to have this workng properly to expose the data.We also leverage AD Groups, which also can have advantages.Fri, 09 Dec 2011 16:59:33 GMTGreg Edwards-268690http://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]Greg Edwards-268690 (12/9/2011)[/b][hr][quote][b]bruce.l.pettus (12/9/2011)[/b][hr]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.[/quote]Any domain admin should recognize kerberos as a much more secure method than allow anonymous.And far more secure than using basic on a web site too.The service accounts [b]and[/b] the machines need to be allowed to delegate.And there is also contrained delegation, which is much more specific.I have never had any push back in my company on using Kerberos.And the domain admins were thankful for my help in setting up and troubleshooting.[/quote]If the service is running under a domain account, the machine account plays no role in kerberos authentication.Fri, 09 Dec 2011 16:48:08 GMTSpringTownDBAhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]ALZDBA (12/9/2011)[/b][hr]Nice article. And what's most important .... It will help people diagnose and solve this kind of issues !With fixed ports, you can also register the spn yourself ( after install of the instance ) using this DOS script[code="plain"]echo offrem http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspxrem To create an SPN for SQL Server, enter the following code at a command prompt:rem setspn -A MSSQLSvc/Host:port serviceaccountrem For example:rem setspn -A MSSQLSvc/server1.redmond.microsoft.com sqlaccount echo onrem C:\Program Files\Support Tools\setspn -A MSSQLSvc/yourservername.yourdomain.yourdomainsuffix:2388 DOMAIN\SERVICEACCOUNT[/code][/quote]So you are a domain admin?Fri, 09 Dec 2011 16:39:53 GMTGreg Edwards-268690http://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]bruce.l.pettus (12/9/2011)[/b][hr]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.[/quote]Any domain admin should recognize kerberos as a much more secure method than allow anonymous.And far more secure than using basic on a web site too.The service accounts [b]and[/b] the machines need to be allowed to delegate.And there is also contrained delegation, which is much more specific.I have never had any push back in my company on using Kerberos.And the domain admins were thankful for my help in setting up and troubleshooting.Fri, 09 Dec 2011 16:36:15 GMTGreg Edwards-268690http://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]Jason Crider (12/9/2011)[/b][hr]Greg, I assume are talking about [url=http://www.microsoft.com/download/en/details.aspx?id=23176]this whitepaper[/url] for Sharepoint 2010?Haven't heard of the KerbBuddy tool.[/quote]Yep - that's the one. 241 pages.:-)And still missing a bit.I think I found KerbBuddy on Codeplex.Fri, 09 Dec 2011 16:29:23 GMTGreg Edwards-268690http://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxHey Kenneth,Thanks for your reply. I apologize if my original post seemed harsh, I've fought some extremely frustrating battles with kerberos in the past and want others to avoid my pain and anguish.As for automatic spn registration, there is a recommendation against using automatic registration on a cluster: [url]http://blogs.msdn.com/b/psssql/archive/2010/03/09/what-spn-do-i-use-and-how-does-it-get-there.aspx[/url] (which I just found about 15 minutes ago, the rationale is in the comments)My original thoughts on manual being better are due to consistency with other Microsoft products that don't auto-register themselves (SSRS, SSAS, Sharepoint 2010 components, etc), and explicit registration forcing DBA's and Sys Admins to become more familiar with kerberos configuration. Unfortunately, the easy way to get spn's registered is to run sql as a domain admin account. The second easiest is to ask a system administrator to run "setspn.exe ...". The third is to grant the specific permissions to the service account (as you mentioned).As for constrained vs unconstrained,the current guidance from MS for Sharepoint configuration strongly pushes constrained delegation. Because constrained -> unconstrained doesn't work (and is very hard to trace as the culprit), I would only recommend configuring constrained delegation on your sql service accounts. I believe that the guidance from MS recommending unconstrained delegation for SqlServer hasn't been revisited in several years (could be wrong though)If you wrote a follow-up article on configuring kerberos for SSRS and/or SSAS, that might be helpful as well. I hate seeing people avoid kerberos because it's too hard to get configured or they don't know how to troubleshoot it. thanks!Fri, 09 Dec 2011 16:10:23 GMTSpringTownDBAhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]SpringTownDBA (12/9/2011)[/b][hr]Wow, lots of misleading information in this article. This is the wrong way to go about setting up kerberos. (I'd equate it to recommending switching to simple recovery mode to shrink the log file. It's bad advice!)[/quote]I'm sorry you feel that way. This was primarily aimed at those people who are having a hard time with this problem and would rather have a kerberos solution than doing something else. Switching to SQL logins for example.[quote]1. -- Not a requirement for kerberos. It could be considered a best practice, but it's not a requirement[/quote]Agreed. As stated earlier in the discussion this is a best practice not a requirement. I've never tried it but at least one person has stated they have and it works.[quote]2. -- NO! Just have a domain admin create the SPN. If you're worried about dynamic ports changing, then assign it a static port.4. -- manual creation of SPN's is a better option.[/quote]Why do you feel manual is better than automatic? To use a similar analogy to what you have above, it would be like manually creating and executing backup scripts rather than using a maintenance plan and a job. Do you have a specific reason for this or is it just an opinion? If I remember correctly the method I gave is the one recommended by Microsoft. I'm always willing to be proven wrong though, and certainly enjoy learning more.[quote]5. -- SVCSQLServerB isn't delegating credentials, so it doesn't need to be trusted for delegation. only SVCSQLServerA needs this set[/quote]If you want to go in both directions (which was my example) then you have to set up both trusts.[quote]6. -- "Trust this user for delegation to specified services only" (constrained delegation) is more secure than "any service" (unconstrained delegation). Kerberos delegation will not let you delegate from constrained to unconstrained (weakening security). [/quote]Agreed. As I stated in my article I had to use "Trust this user for delegation to specified services only" and it is certainly more secure. I included both options since as I recall trusting all is the method that Microsoft recommended.Fri, 09 Dec 2011 15:00:41 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxThis caught my eye, right away. I've worked in the Microsoft Dynamics world, and mostly with MSCRM.We have been dealing with these issues from years now and some scenarios are very difficult.One instance was a Win 2003 full forest level AD that handled Kerberos just fine. But a Windows 2000 forest in Europe has a forest trust to the Win2003 forest. Sadly, there are no Kerberos packets allowed with a Windows 2000 trust. Thus , SSRS reports could not be rendered from that Forest. SPNs could not be resolved from any clients on that side of the trust.The story is dramatized here:http://www.windowsitpro.com/article/reporting-services/twelve-angry-techsFri, 09 Dec 2011 13:29:00 GMTosgcurthttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxYou haven't broken it for the average user.If you logged into windows as the sql service account, then double hops won't work.Fri, 09 Dec 2011 13:17:45 GMTSpringTownDBAhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxI recently requested "account is sensitive and cannot be delegated" for my SQL service accounts. Did I shoot myself in the foot and unintentionally break account delegation for the accounts?Fri, 09 Dec 2011 13:05:54 GMTbruce.l.pettushttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxWow, lots of misleading information in this article. This is the wrong way to go about setting up kerberos. (I'd equate it to recommending switching to simple recovery mode to shrink the log file. It's bad advice!)[quote]1. Make sure that the SQL Server instances, SQLServerA and SQLServerB, each have their own service accounts; say SVCSQLServerA and SVCSQLServerB. Each instance should have its own service account. 2. You must grant Read and Write ServicePrincipalName1 for both instances. This is what allows SQL Server to create & remove SPNs. 3.Assign the service accounts as the startup accounts for the SQL Server instances. SVCSQLServerA for SQLServerA and SVCSQLServerB for SQLServerB. 4. Restart the SQL Server Instances. This causes a SPN to be created (Service Principal Name) for each instance. 5. Once this is done a "Delegation" tab will be visible in AD for each of the service accounts. Grant both of your service accounts "Trust this user for delegation to any service (Kerberos only)". 1 6. Now if your security people balk at the "any service" part, like mine did, they can grant the trust just to the other service account.i.e. For SVCSQLServerA grant "Trust this user for delegation to specified services only"/"Use Kerberos only"/SVCSQLServerB and vice versa. 2 [/quote]1. -- Not a requirement for kerberos. It could be considered a best practice, but it's not a requirement2. -- NO! Just have a domain admin create the SPN. If you're worried about dynamic ports changing, then assign it a static port.3. -- ok.4. -- manual creation of SPN's is a better option.5. -- SVCSQLServerB isn't delegating credentials, so it doesn't need to be trusted for delegation. only SVCSQLServerA needs this set6. -- "Trust this user for delegation to specified services only" (constrained delegation) is more secure than "any service" (unconstrained delegation). Kerberos delegation will not let you delegate from constrained to unconstrained (weakening security). [hr][size="3"]Requirements for Kerberos:[/size]1. To connect to a service using Kerberos, you need a ticket from the ticket granting service (Domain Controller). The ticket is encrypted with your user account private key and the target service account's public key (so, only the target service account's private key can decrypt it, and it can verify your identity by decrypting using your public key.) 1a) the DC needs to know which user account is going to decrypt the ticket. Your ticket request says 'I want to connect "MSSQLSvc" on machine "SQLServerA" ' [b]This is what the SPN is used for[/b]. No SPN, No ticket. Wrong account in the SPN, and the target account can't decrypt the ticket (KRB_ERR_MODIFIED) 1b) Kerberos only works with A records in DNS, not CNAME records for SPN's. 1c) the NetworkService account is the computer account (mydomain\SQLServerA$) So if you're running as network service, register the SPN under the computer account and Kerberos will work.2. If the Service needs to pass your credentials on to another service (Delegate), then the service account that's delegating needs to be trusted for delegation. 2a) Unconstrained -> constrained (tightening security) is allowed. Constrained -> Unconstrained (loosening) is not allowed 2b) Sensitive accounts can be flagged to not allow delegation. Example: User (internet explorer)-> Sharepoint (HTTP on server WEB1 running as SVCSharepoint) -> SSRS (HTTP on server RS1 running as SVCReports) -> SQL1 (MSSQLSvc on server SQL1 running as SVCSqlServer1) -> (linked server) SQL2\INSTANCENAME (MSSQLSvc on server SQL2 running as SVCSqlServer2)WEB1,RS1,SQL1 and SQL2 all need "A" records in DNS SVCSharepoint, SVCReports and SVCSqlServer1 need to be "trusted for delegation"spn's should be created with/without FQDN's including ports (or instance names):[code]setspn.exe -U -A http/WEB1.myDomain.local myDomain\SVCSharepointsetspn.exe -U -A http/RS1.myDomain.local myDomain\SVCReportssetspn.exe -U -A MSSQLSvc/SQL1.myDomain.local:1433 myDomain\SVCSqlServer1setspn.exe -U -A MSSQLSvc/SQL1:1433 myDomain\SVCSqlServer1setspn.exe -U -A MSSQLSvc/SQL2.myDomain.local:INSTANCENAME myDomain\SVCSqlServer2setspn.exe -U -A MSSQLSvc/SQL2.myDomain.local:56542 myDomain\SVCSqlServer2setspn.exe -U -A MSSQLSvc/SQL2:INSTANCENAME myDomain\SVCSqlServer2setspn.exe -U -A MSSQLSvc/SQL2:56542 myDomain\SVCSqlServer2[/code]No duplicate SPN's (two different accounts registered for the same SERVICE\HOST combo) It [b]is[/b] ok for the same account to be used in different places. To query for duplicates:[code]setspn.exe -X[/code]No additional config is required for SqlServer. For other services (SSRS, IIS, Sharepoint, WCF services) you have to explicitly enable kerberos authentication. check msdn for instructions.Fri, 09 Dec 2011 12:14:18 GMTSpringTownDBAhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]Jeff Moden (12/9/2011)[/b][hr]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>[/quote]Yes. Also, see my post above where I address a known issue with MS when the double-hop occurs over same nodes in a windows cluster. Because windows wasn't releasing DNS settings properly after a fail over, kerberos authentication doesn't happen, and it uses NTLM. This works fine for most operations until it performs something like the bulk-insert and then fails with access-denied. What is frustrating is that the double-hop actually would work if I performed the same type of operation (in SSMS I performed a bulk insert to replicate the failure) from another server on the network, or on the same node. Except it failed when I was on the opposite node of the cluster. I then saw that when connected from non-clustered machine, the session was kerberos. However on the other node in the cluster it was NTLM (when it should have been kerberos).Fri, 09 Dec 2011 11:56:43 GMTC_Susihttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxRecently came across a bug when you need to do a double-hop over different nodes in the same Windows 2008 cluster. If you have two instances of SQL on the same Windows 2008 cluster, and do something that requires double-hop authentication (in my case SQL Agent initiated an SSIS package from instance A that initiates a bulk insert on Instance B of a file on Server X) there is a bug where Windows will initiate an NTLM authentication even though both instances are on different nodes of the cluster. This happens because Windows does not properly release the DNS entries when it performs a fail over, therefore Kerberos fails initially so it fails over to NTLM authentication. So when the instances are running on the same node, it works great (no double-hop). But when they are on different nodes, it requires double-hop, and therefore fails with an access denied on the bulk-insert statement. The access denied becomes a red-herring that it is a file permissions issue, and it is not. Also, it is not "something you probably configured wrong" as one person in my organization tried to insinuate. It is a known issue with MS and addressed in KB 982549. I hope I just saved somebody several days and half a head of hair.Fri, 09 Dec 2011 11:42:33 GMTC_Susihttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxNice article. And what's most important .... It will help people diagnose and solve this kind of issues !With fixed ports, you can also register the spn yourself ( after install of the instance ) using this DOS script[code="plain"]echo offrem http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspxrem To create an SPN for SQL Server, enter the following code at a command prompt:rem setspn -A MSSQLSvc/Host:port serviceaccountrem For example:rem setspn -A MSSQLSvc/server1.redmond.microsoft.com sqlaccount echo onrem C:\Program Files\Support Tools\setspn -A MSSQLSvc/yourservername.yourdomain.yourdomainsuffix:2388 DOMAIN\SERVICEACCOUNT[/code]Fri, 09 Dec 2011 11:21:34 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxNice. Thanks Kenneth! I've had the issue before and worked around it differently (with poorer solutions), but this seems like a great solution. Next time I have the problem, I'm coming right back here to follow your steps. Thanks!Fri, 09 Dec 2011 10:08:48 GMT Chad Crawfordhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxWhile everyone is discussing this I'm going to throw out a problem I had while working on this that I never really understood. I had a server where when I configured it to a static port, I forgot to remove the entry under Dynamic Port in configuration manager. The two port numbers of course were different.The SPN created was under the static port number, and everything I looked at showed SQL running under that port number. However about half of my other servers wouldn't "hop" to this server until I removed the dynamic port entry and restarted the instance. The other half of my servers worked just fine.Does anyone have any idea why?Fri, 09 Dec 2011 09:51:23 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]bruce.l.pettus (12/9/2011)[/b][hr]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.[/quote]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.Fri, 09 Dec 2011 09:46:49 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxAnother 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.Fri, 09 Dec 2011 09:46:47 GMTnathan 7372http://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]chinn (12/9/2011)[/b][hr]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,[/quote]chinn, good question, you can see the Russell Fields' answer on page 2. Honestly I didn't know myself :)Fri, 09 Dec 2011 09:42:33 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxThere 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.Fri, 09 Dec 2011 09:41:39 GMTbruce.l.pettushttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]bruce.l.pettus (12/9/2011)[/b][hr]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.[/quote]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.Fri, 09 Dec 2011 09:33:36 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxI'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.Fri, 09 Dec 2011 09:28:27 GMTbruce.l.pettushttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxKenneth, 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.Fri, 09 Dec 2011 08:58:42 GMTmr.lenthttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxGood 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.Fri, 09 Dec 2011 08:46:39 GMTjlivengoodhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxKenneth, 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,Fri, 09 Dec 2011 07:48:24 GMTchinnhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]Jeff Moden (12/9/2011)[/b][hr]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>[/quote]It should work, even though I haven't tried it myself.According to BOL for BULK INSERT:[quote]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.[/quote]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.Fri, 09 Dec 2011 07:17:21 GMTNils Gustav Stråbøhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxI 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.Fri, 09 Dec 2011 07:13:10 GMTdmbakerhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspxI'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.-GrubbFri, 09 Dec 2011 07:10:17 GMT@SixStringSQLhttp://www.sqlservercentral.com/Forums/Topic1219052-1186-1.aspx[quote][b]Greg Edwards-268690 (12/9/2011)[/b][hr]...Good job giving a short overview. Many give up trying to set this up, and resort to workarounds. [/quote]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.Fri, 09 Dec 2011 07:05:53 GMTEric M Russell