SOLVING AUTHENTICATION ERRORS ON MS SQL WHILE SETTING UP XENDESKTOP 7.6

Source: My Virtual Vision by Kees Baggerman

DuringSQL a lab setup of XenDesktop 7.6 I used a Microsoft SQL 2008 R2 instance which I installed before while setting up the rest of my lab environment. While the database setup worked seamlessly for other environments it seemed that I couldn’t access the SQL server from the XenDesktop Setup wizard.

I first tried the obvious things, using a service account didn’t help. After that I tried the SA account (just to see if it was an actual issue with the rights on the service account) but that didn’t work either.

The issue:

I couldn’t create the database from the XenDesktop wizard, I tried several accounts but they couldn’t connect to the database or didn’t have the rights the access the database server.

Troubleshooting:

Apparently changing the user didn’t had the effect I wanted, I logged on to the SQL just to make sure my SA password was still valid and it was because I was able to logon to the SQL management Studio with the SA credentials. Because I was already logged on the SQL server I just went on opening the event viewer and found the following errors:

Screen Shot 2014-11-10 at 10.29.05

SQL

My friend Google then found the following topic:SSPI handshake failed with error code 0x8009030c, which led me to the How to Configure an SPN for SQL Server Site Database Servers. It seems that during the installation of SQL the SPNs for SQL server weren’t registered.

Solving this issue:

With the command ‘setspn -L %hostname%’ you can list the SPNs that are registered for a certain server.

v_3_Screen Shot 2014-11-10 at 10.29.43_v2

When I did this for my SQL server it didn’t list the SQL services so I had to register the SPN manually. Again I googled and found the following article:Register a Service Principal Name for Kerberos Connections.

This article described the following switches to manually register the SPN:

To register the SPN manually, the administrator must use the Setspn.exe tool that is provided with the Microsoft Windows Server 2003 Support Tools. For more information, see the Windows Server 2003 Service Pack 1 Support Tools KB article.
Setspn.exe is a command line tool that enables you to read, modify, and delete the Service Principal Names (SPN) directory property. This tool also enables you to view the current SPNs, reset the account’s default SPNs, and add or delete supplemental SPNs.
The following example illustrates the syntax used to register manually register an SPN for a TCP/IP connection.
setspn -A MSSQLSvc/myhost.redmond.microsoft.com:1433 accountname
Note If an SPN already exists, it must be deleted before it can be reregistered.You do this by using the setspn command together with the -D switch. The following examples illustrate how to manually register a new instance-based SPN.For a default instance, use:
setspn -A MSSQLSvc/myhost.redmond.microsoft.com accountnameFor a named instance, use:
setspn -A MSSQLSvc/myhost.redmond.microsoft.com:instancename accountname

So I ran the command:

‘setspn -a MSSQLSvc/SQL001:1433 administrator’

The following screen output appeared:

Screen Shot 2014-11-10 at 10.30.01

After I registered the SPN for the SQL Server I listed the SPNs of the server again and the SQL service was registered. After a reboot I was able to connect to the database from the XenDesktop wizard.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s