Auto Generating of Service Principal Name (SPN) Commands

Hello All,

Have you had to use Kerberos authentication and needed to create the commands for the Service Principal Name (SPN)? Then the script in this blog will help. Execute the script on the SQL Server with SQL Server Reporting Services (SSRS) and it will automatically generate the commands needed. 

If you need more details on Kerberos have a look at Rob Carrol blog. It is very good and covers how to enable Kerberos authentication for reporting services. Here is his link.
http://blogs.technet.com/b/rob/archive/2011/11/23/enabling-kerberos-authentication-for-reporting-services.aspx

Now on to my script.

First, load this script on to your server (please verify on test servers) and execute. It only creates temp tables, does not write anything to SQL Server and the temp tables are deleted once executed.

The script will automatically detects the SQL Server settings

******** Settings that have been automatically detected **************

Result for detection of SQL Server name --> SrvName
Result for detection of Service Account name -->MyDomain\svcAccount
Result for detection of Port Number --> 1433
Result for detection of default Domain Name --> MyDomain
Result for detection of cluster --> SQL Server is not clustered
Result for detection of Kerberos --> TCP is using Kerberos
Result for detection of IP Address --> 192.168.54.1


Next, the script will take the information found and create the SETSPN commands 


cls
echo '*** Automatic SQL Server Service Principal Name (SPN) ***'
echo ' '
echo 'Setting SQL Server Database SPN...please wait'
echo ' '
  
SetSPN -s "MSSQLSvc/SQLServerName:1433" "MyDomain\svcAccount"
SetSPN -s "MSSQLSvc/SrvName.MyDomain.com:1433" "MyDomain\svcAccount"


echo ' '
echo 'Setting SQL Server Reporting Services SPN...please wait'
echo ' '

SetSPN -s "http/SrvName" "MyDomain\svcAccount"
SetSPN -s "http/SrvName.MyDomain.com" "MyDomain\svcAccount"


echo ' '
echo 'Setting SQL Server Analysis Services SPN...please wait'
echo ' '

SetSPN -s "msolapsvc.3/SrvName" "MyDomain\svcAccount"
SetSPN -s "msolapsvc.3/SrvName.MyDomain.com" "MyDomain\svcAccount"

echo ' '
SetSPN -l MyDomain\svcAccount"
echo ' '

           ==> Auto SPN generation is now complete <==

Finally, copy the SetSPN statements and execute then with a Domain Admin account.  Verify that the account "MyDomain\svcAccount" has the following check on "Trust this user for delegation to any service [Kerberos only]" 

Note that once the SetSPN command is executed it may take several hours (once mine took approx. 8) before it takes effect.

Here is the link for my script
https://drive.google.com/file/d/0B07PMQYOhF2DMEJMcnpGVDg3eFE/view?usp=sharing

Thanks,

Rudy

SQL Server Performance Survival Guide

Hello and Happy New Year!

Today's blog is a link to the Microsoft Tech Net site that has an excellent guide regarding SQL Server performance. It is worth a look and a place in your browser's bookmarks.

http://social.technet.microsoft.com/wiki/contents/articles/5957.sql-server-performance-survival-guide.aspx

Below is the table of contents

Learning about Troubleshooting SQL Server Performance
Transaction Processing (OLTP)
Data Warehouse Performance
Troubleshooting Hardware Issues
Monitoring and Performance Tools
Performance Tips and Tricks
Performance Topics in SQL Server Books Online
Getting Advice and Guidance
SQL Server Customer Advisory Team
Community Resources
SQL Server Performance Blogs
SQL Server Performance Forums
SQL Server Resource Centers
SQL Server Performance Workshops
SQL Server Social Networking
Twitter
SQL Server Performance on Facebook
SQL Server Performance Books

Hope you find this useful,

Rudy