0660 Other Linked Server Back-end Databases

0660.1 Is this the correct workflow?

Does the issue only affect database connections, or does it affect web and file share connections, too?

0660.1.1 Typical Error Messages

• Cannot generate SSPI Context
• Login failed for user '(null)'
• Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Note: Errors connecting to or authenticating to Windows Azure SQL Database are handled by the SQL Azure support team.

0660.1.1 Is there a better workflow?

• If the issue is intermittent and not consistent, use 0300 Intermittent or Periodic Network Issue, including connection timeouts.
• Does the issue happen with a simple client-server connection? If yes, use 0400 Consistent Authentication Issue.
• Is the error a connection timeout or other network failure? If yes, use one of the networking workflows.

0660.2 Moving Parts

The initial goal is to try to isolate whether either or both the SQL Servers is not accepting Kerberos credentials. If there are problems after that, we have additional troubleshooting steps.

Here is the conceptual diagram of Kerberos delegation.

0660.3 Pre-Work

0660.3.1 Please perform the initial data collection and narrowing steps:

0100 Initial Data Collection and Scoping Questions

This will help get a macro perspective of the scope of an issue, such as whether the issue affects multiple computers or just one, or whether only those computers in a specific data center are facing issues. This can help focus the troubleshooting steps. It will also make you prepared for discussing the issue with Microsoft Support should you choose to do so.

0660.4 The Provider does not appear in the list of Linked Server Providers.

There are a number of causes for this:

0660.4.1 If it is a built-in Provider, such as Jet or the Microsoft provider for Oracle, then these providers are 32-bit only and will only show in 32-bit versions of SQL Server. They will not show in 64-bit versions. This is By Design. You should choose a different Provider, e.g. Oracle's 64-bit Provider. As a last resort, the 64-bit SQL can do a linked server connection to a 32-bit SQL Server that uses the 32-bit-only Provider, it is not recommended if there is any other solution.

0660.4.2 If it is a provider that does not ship with Windows, validate that it has been installed correctly (UDL file) and that it's processor architecture (32-bit or 64-bit) matches the SQL Server's.

0660.4.3 Validate that the SQL Server being connected to via SSMS is running on the machine you installed the Provider on. e.g. If the SQL Server is in a Cluster, make sure the provider is installed on ALL cluster nodes and that it is installed on the current active node especially. Even if not a cluster, check whether the connection string could accidently be pointing to the wrong SQL Server; many installations have similar names, e.g. SQLPROD03 and SQLPROD04.

0660.5 Linked Server to Access, Excel, or Text files.

Two Caveats:

1. Using the Access 2007 (ACE) and later Providers in IIS or SQL Server are specifically not supported. This is documented on the download page:
   http://www.microsoft.com/en-us/download/details.aspx?id=13255 (2010)
   https://www.microsoft.com/en-us/download/details.aspx?id=54920 (2016)

2. Using the Access ODBC Driver, Excel ODBC Driver, Text ODBC Driver, which all rely on Access are not supported in IIS or SQL Server because they are not thread safe and could crash the server. See KB 299973.

Using the JET 4.0 OLE DB Provider is supported, though not recommended. It is deprecated, 32-bit only, and should not be used in new applications. Ideally, application developers should upload the data into a SQL Server table from the client application or via a macro in Excel or Access, or via an SSIS job.

Access/JET do not support credential delegation, so the files need to be local to the SQL Server and not on a remote machine. Copy the file locally and create a linked server to it. If that works, leave the files local.

If the file must be remote, you can set up constrained delegation (for "Any") to the file share. Use the file server's HOSTS SPN in Active Directory.

Any issues are unsupported by Microsoft.

0660.6 Run out-of-process.

Many providers are not safe to run in-process with SQL Server. In the Provider Properties, make sure Allow Inproc is unchecked.

0660.7 Oracle

0660.7.1 The Oracle Client software or ODAC software needs to be installed on the SQL Server machine. Make sure the 64-bit OLE DB Providers are installed. You should not be developing on the SQL Server machine as most development tools are 32-bit and while having both 32-bit and 64-bit client software on the machine is supported, it can occasionally lead to problems.

0660.7.2 Validate the connection can be made using Oracle tools, such as SQL*PLUS. It is up to the you or the Oracle DBA to fix any tnsnames.ora settings. If necessary, consult with Oracle or check Oracle knowledge base articles.

0660.7.3 Validate the Oracle Provider (not Microsoft's one) can be seen in the UDL file list of Providers. If not, make sure the 64-bit client is installed.

0660.7.4 Once the UDL file can connect, then you can add a Linked Server. Make sure the Oracle Provider shows in SSMS in the list of Linked Server properties. Make sure you uncheck Allow In-proc.

0660.7.5 Oracle will typically use a hard-code username and password. These are local Oracle accounts and not Windows/domain accounts. If using Integrated security, then consult your Oracle DBA or Oracle support regarding SPNs.

0660.7.6 Oracle errors follow this format: ORA-99999. You can look these codes up on the internet to see typical causes.

0660.8 Providers supported by other teams at Microsoft

0660.8.1 The Microsoft IBM DB/2 provider is supported by the HIS team.

0660.8.2 The Exchange provider is supported by the Exchange team.

0660.8.3 The Commerce Server provider is supported by the Commerce Server team.

0660.8.4 The Active Directory provider is supported by the Active Directory team.

0660.9 Performance Scenarios

Note: The remote query engine in SQL Server is a generic OLE DB client. As such, the API calls to the 3rd-party Provider may not be as efficient as you could program yourself in a dedicated application.

0660.9.1 Performance scenarios of 3rd-party Providers are generally supported by the vendor, or the Provider support team (prior section).

0660.9.2 You can try to test the provider performance outside of SQL Server. Many 3rd-party interactive clients may return after reading the first 100 rows, or so, and fetch the remainder on a background thread, so may not be a reliable measure. A custom VBScript should be more reliable.