File browser does not work for remote servers

Category: Troubleshooting & error messages
Date: 17 Aug 2009
Product: SQL Backup
After adding a server to the SQL Backup file browser while connected using Windows authentication, it may report the following:
"Cannot access resource. Check that you have the correct permissions to view this resource."

Please note that the following only applies to SQL Backup console connections that use Windows security. If your connection uses SQL authentication, please see http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Backup&c=knowledgebase%5cSQL_Backup%5cKB200903000369.htm . This article also contains a per-user workaround that you can use if you do not have the appropriate permissions on the Active Directory server to add a SPN.

This issue is caused by Windows security restrictions that prevent a "double hop" of credentials from one server to another, using an intermediate server. In this case, the credentials are passed to the SQL Server, then back to the client workstation that is running SQL Backup. The solution to this problem is to ensure that the authentication mechanism used by SQL Backup is Kerberos rather than NTLM, which is used by most SQL connections using Windows authentication.

To switch to Kerberos, first register a Service Principal name for the SQL Server Service, then use Active Directory to trust the computer and any accounts for delegation.

To register the Service Principal Name for SQL Server, first download and install the SetSPN utility on the SQL Server. You can get this from the Windows Server Resource kit or directly from http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46 .
After the installation, open a command prompt and change directory to the directory where the resource kit is installed and run this command:

c:\windows\resource kit>SetSPN -A MSSQLSvc/server.domain.local:1433 SERVER

Note that in the command above, server.domain.local is the fully-qualified domain name of the server, and SERVER is the NETBIOS name of the server. The server must have a fully-qualified DNS entry. To ensure this, ping the IP address of the machine:
  · use ipconfig to get the IP address
  · ping -a <IP address>
The ping should return a full-qualified domain name for the server. If it does not, contact your network administrator to get the server added to your network's Domain Name Server.

The next step is to trust the computer for delegation. To do this, you must have domain administrator privileges and access to the "Active Directory Users and Computers" applet on the Windows Active Directory server containing the computer account. If the SQL Server Service runs as the "Local System" account, then all that is necessary is to trust the computer for delegation. Note that on SQL Server clusters, this procedure must be done on the computer accounts of all nodes participating in the clustering.

  · Open Active Directory Users and Computers and locate the computer account of the SQL Server. Right-click the Computer object and select "Properties".
  · Click the "Delegation" tab
  · Select the option "Trust this computer for delegation to specified services only"
  · Click "Add", then "Users or Computers" and select the SQL Server computer.
  · If the SetSpn utility in the first step had been successful, then a service type called MSSQLSvc should exist. Select this and click OK, the OK again.

If the SQL Server Service runs as a domain account, then the same basic procedure has to be performed for the user account. Locate the SQL Server Service account in the "Active Directory Users and Computers" applet on the Active Directory server and open its' properties. Select the "Account" tab, and ensure that the "Account is sensitive and cannot be delegated" is not selected, and where applicable, "Account is trusted for delegation" is selected.