SQL SERVER – FIX – Linked Server Error 7399 Invalid authorization specification

I have personally seen when people use Linked Server there are a number of issues from authentication to performance. These issues are part of working with something that has so many variations and permutations of actually going wrong. Many a times I suggest people to keep away from it as much as possible. But it cannot happen all the times and there are code blocks in their application that relies on this as a requirement.

This is one of the common error I see in various forums. When someone is new to SQL Server and tried to create a linked server, he/she would just give server name and choose SQL Server as server type and hit OK. But SSMS would provide below error

SQL SERVER - FIX – Linked Server Error 7399 Invalid authorization specification linked-error-01

TITLE: Microsoft SQL Server Management Studio
——————————
The linked server has been created but failed a connection test. Do you want to keep the linked server?
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
The OLE DB provider “SQLNCLI11” for linked server “BIGPINAL” reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider “SQLNCLI11” for linked server “BIGPINAL”.
OLE DB provider “SQLNCLI11” for linked server “BIGPINAL” returned message “Invalid authorization specification”. (Microsoft SQL Server, Error: 7399)
——————————
BUTTONS:
&Yes
&No
——————————

Above message has two messages

  1. A Question: The linked server has been created but failed a connection test. Do you want to keep the it?
  2. An Error: Microsoft SQL Server, Error: 7399 – The OLE DB provider “%ls” for linked server “%ls” reported an error. %ls

We can click Yes and linked server would be created but it won’t work. Once we click on Test Connection as shown below – we would get same error what we got during creation.

To fix this problem, we need to understand little about connectivity and authentication. Whenever we connect to SQL, there are two ways

  1. SQL Authentication: To use this, we need to provide login name and password while connecting to SQL Server. These account are stored within SQL Server.
  2. Windows Authentication: While using Windows operating system, we can use logged in account to SQL and it would allow us to connect to SQL Server without providing password because we have already logged into operating system and have been authenticated by Windows.

When the linked server is created, the default values under security is “Be made without using a security context” – this is the cause of the problem.

Solution: Choose one of the two from below highlighted.

SQL SERVER - FIX – Linked Server Error 7399 Invalid authorization specification linked-error-02

Have you seen this earlier? How would you provide windows account here? What are your opinion? Do let me know via comments below.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER 2016 – How to Use SQL Server 2016 – Stretch Database – Notes from the Field #127
Next Post
SQL SERVER – FIX – Agent XPs Component is Turned Off as Part of the Security Configuration for this Server

Related Posts

6 Comments. Leave new

  • Make sure the provider has “Allow inprocess” ticked in the settings

    Reply
  • steveculshaw
    May 8, 2018 6:56 pm

    Thanks for the blog post …

    Changing to either of the options changes the error to …
    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. (Microsoft SQL Server, Error: 18456)
    Login failed for user ‘mydomainmyaccount’. Reason: Attempting to use an NT account name with SQL Server Authentication

    Any pointers ?

    Reply
    • “ANONYMOUS LOGON” logon failure is mostly due to incorrect SPN.

      Reply
    • Hey Steve – just had the same issue. The solution for me was to use a SQL Server login in the bottom option. (note that the sql server login must be valid in the target server). Cheers.

      Reply
  • Thank you kindly for the help – fixed the challenge.

    Reply
  • Thank you very much for the help you provide in the blog Pinal.
    And thank you for your comments guys, you really helped me to solve the issue i used to have :)

    Reply

Leave a Reply

Menu