Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»

Configuring Kerberos Authentication Expand / Collapse
Author
Message
Posted Monday, March 22, 2010 5:26 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 405, Visits: 1,136
Hi Brian.

I've checked both servers are using configured for Kerberos, both SQLTest01 and SQLTest02 return the following NTAuthenticationProviders:

NTAuthenticationProviders : (STRING) "Negotiate,NTLM"


I have some more information, which I hope is helpful:

1. When I checked it first thing this morning, the 'live' reporting services was running on SQLTest01.

I could logon and run reports successfully using Kerberos authentication, using all of the following:
- sqltest01
- sqltestrs.forthprots.net ('live' report server dns entry)
- iristestrs.forthprots.net ('live' report server dns entry)
- scatstestrs.forthprots.net ('live' report server dns entry)

All of these tested successfully, from my laptop logged on as my standard domain account.

I then failed-over the report server to run SQLTest02. I could run reports if I connected to sqltest02/reports, which successfully authenticated in Kerberos, but not using
- sqltestrs.forthprots.net ('live' report server dns entry)
- iristestrs.forthprots.net ('live' report server dns entry)
- scatstestrs.forthprots.net ('live' report server dns entry)


After a period of time, this then worked?? is this a ticket issue?

(I purged this tickets on the server)


2. Having switched back to sqltest01, to try and find out what is different, SQLTest 01 no longer worked in kerberos authentication?


On the sqltest01 server I purged the tickets, using kerbtray and if I logged on to sqltest01/reports, I can see a ticket pop up for MSSQLSvc/iris_test.forthports.net:1433 (my sql server data source).

Back on my laptop, when I connect to sqltest01/reports and run a report, it run the report, using kerberos authentication, but for my admin account DL.Admin(but I'm logged on as my standrard account - DL.Standard)??

I logged on to the server sqltest01 as DL.Admin, but why is it picking up when I access the report server from my laptop?

I noticed though, that a new ticket didn't appear in kerbtray. (note the report I running, runs the query from your article, to define what time of connect I have with the database)

Let me know if I not using the kerbtray right, but what I do, to test tickets are being issued, is on the machine I access report server from, I purge the tickets, and then connect.

confused and dazed - Dave



_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #887282
Posted Tuesday, March 23, 2010 5:10 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 405, Visits: 1,136
I have noticed one other problem as well, running on SQLTEST02.

After switching Reporting Services to run on SQLTest02 and connecting to it using one of my DNS entries:
- sqltestrs/reports
- iristestrs/reports
- scatsrs/reports

I get the Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

If I logon via sqltest02/reports I can run the report and it authenticates using kerberos.

I can then go back to using one of:
- sqltestrs/reports
- iristestrs/reports
- scatsrs/reports

and the report will run successfully, even after closing my web browser and launching a new web browser session?

It would appear I get a ticket for HTTP/sqltest02.forthports.net - which would possible explain why it works, but why is it not working for my dns entries?

I had to configue Reporting Services to RSWebApplication.config file <ReportServerUrl> to:
	<UI>
<ReportServerUrl>http://sqltest02/ReportServer</ReportServerUrl>
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
</UI>

as the reporting services website is not the default website. (I also setup the host headers)

Is the config file part of the reason why it only generates a ticket for sqltest02 and not my dns references, that I have created http SPN's for?


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #887985
Posted Tuesday, March 30, 2010 9:41 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 405, Visits: 1,136
I've now resolved the issue.

For anyone else who has issues configuring Kerberos authentication, take a look at this white paper, which helped me to get to the bottom of my issues.

http://www.microsoft.com/DownLoads/details.aspx?FamilyID=99b0f94f-e28a-4726-bffe-2f64ae2f59a2&displaylang=en

which I found on another useful site: http://callumhibbert.blogspot.com/2009/02/kerberos-delegation-and-sql-reporting.html


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #892958
Posted Tuesday, July 27, 2010 10:44 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:06 AM
Points: 27, Visits: 462
Harold Buckner (12/11/2008)
Bradley Deem (12/11/2008)

I have the EXACT same problem with Kerberos. Resulting in the NT AUTHORITY\ANONYMOUS LOGON. It does work though, because I'm able to connect from a Web Server to the SQL server using Kerberos and from the Web Server to SSRS on another server. It just breaks like Harold described above. Log off/Log on to resolve.



I'm glad I'm not the only one.


Well written article. I followed the instruction. Set up SPN on where I can see correct SPN using SETSPN -L
My linkserver between the two hosts (SQL2005) using windows authentication (impersonation) is still getting same error:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.


Jason
http://dbace.us
Post #959552
Posted Tuesday, July 27, 2010 1:20 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
jswong05 (7/27/2010)
Harold Buckner (12/11/2008)
Bradley Deem (12/11/2008)

I have the EXACT same problem with Kerberos. Resulting in the NT AUTHORITY\ANONYMOUS LOGON. It does work though, because I'm able to connect from a Web Server to the SQL server using Kerberos and from the Web Server to SSRS on another server. It just breaks like Harold described above. Log off/Log on to resolve.



I'm glad I'm not the only one.


Well written article. I followed the instruction. Set up SPN on where I can see correct SPN using SETSPN -L
My linkserver between the two hosts (SQL2005) using windows authentication (impersonation) is still getting same error:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.


I didn't cover Kerberos delegation. Both SQL Servers will need to be configured for SQL Server Authentication. Also, the initial SQL Server contacted will need to be set up to use Kerberos delegation:

How to Implement Kerberos Constrained Delegation with SQL Server 2008


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #959647
Posted Wednesday, July 28, 2010 10:03 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:06 AM
Points: 27, Visits: 462
The Microsoft publication is great. But after I set SPN for the service account, I went to AD (based on page 6, Figure 2), the registered SPN entry is not there. There is no way to grant delegation.
Anybody has any experiences on this?
We had to use designated slq login on linkserver as workaround. (that does not meet single sign on principle)

by the way, after SPN registering, the entry will only show up under SETSPN -L domain\serviceaccountuser
not under SETSPN -L hostname
Is that normal?


Jason
http://dbace.us
Post #960173
Posted Tuesday, October 19, 2010 10:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:54 AM
Points: 194, Visits: 322
I recently had a problem where a SQL Server was falling back to NTLM. The solution was rather simple, and in case its of help (I don't think I've seen it mentioned in this yet) - check for duplicate SPN's.

setspn -Q MSSQLSvc/servername.blah:1433

Showed me that another accountname had the same SPN registered. After deleting the duplicate SPN, querying sys.dm_exec_connections reported any new connections using Kerberos auth scheme.



-Ken
Post #1007114
Posted Friday, March 25, 2011 3:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 28, 2011 2:06 AM
Points: 8, Visits: 10
For sql service domain accounts that are NOT domain admins you can configure the service account(s) to create its own SPN on startup so you dont have to worry about the correct syntax for creating the SPN yourself (and also whether or not to create multiple SPNs for clustered instances). If you have 50, 100, 200 sql servers do you really want to be manually configuring/administrating SPNs ?

For each sql service account (which can be running 1 + N sql services) ask the domain administrator to ensure delegation is selected in the domain user account settings. Also you will need to request (or do this yourself CAREFULLY if you have privileges) that your domain admin make the following changes in AD :

Using ADSIEDIT for the sql service account (s) grant permissions to SELF for the following properties :

Read servicePrincipalName
Write servicePrincipalName

This way the SQL server instance will create its own correctly formatted SPN at startup, sql restart is required using this method. Also sql server will not perform any maintenance on this or other SPNs so if you have a large server base you should consider listing the current SPNs to determine if any are redundant or no longer required.
Post #1083831
Posted Friday, March 25, 2011 3:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 1:48 AM
Points: 369, Visits: 105
great article but I have a question.
I configured a sql instance like mentioned in the article and see all connections coming in with Kerberos auth.
However the connections coming from his own (sqlagent & OS) are still NTLM. What can be the cause of this?



Post #1083846
Posted Friday, March 25, 2011 8:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 8:26 AM
Points: 19, Visits: 79
Brian you should contact http://www.devproconnections.com/ and have this article printed. What a great piece of writting.

I ran your T-SQL on a SharePoint Content DB ---- WSS_Content DB and I was suprised to see auth_scheme is KERBEROS which is great news.

However, my question is I did not configure that, so I am assuming this is something SharePoint 2010 does. Thank you so much for the article.

I will keep this in my Favorites for SQL Server when configuring SSRS. You made the start of my day nice with my cup of coffee.
Post #1084070
« Prev Topic | Next Topic »

Add to briefcase «««56789»»

Permissions Expand / Collapse