Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Configuring Kerberos Authentication


Configuring Kerberos Authentication

Author
Message
dave-dj
dave-dj
SSC-Addicted
SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)

Group: General Forum Members
Points: 444 Visits: 1149
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)
dave-dj
dave-dj
SSC-Addicted
SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)

Group: General Forum Members
Points: 444 Visits: 1149
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)
dave-dj
dave-dj
SSC-Addicted
SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)

Group: General Forum Members
Points: 444 Visits: 1149
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)
jswong05
jswong05
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 476
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
:-P
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6820 Visits: 1917
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
@‌kbriankelley
jswong05
jswong05
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 476
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
:-P
spongemagnet
spongemagnet
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 345
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
danny.thomas 57541
danny.thomas 57541
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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.
Ludo Bernaerts
Ludo Bernaerts
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 160
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?



moojjoo
moojjoo
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 120
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search