Ms Sql Linked Server Authentication Error
SERVER - Linked server creation error: OLE DB provider "SQLNCLI11" for linked server returned message "Invalid authorization specification" October 3, 2015Pinal DaveSQL, SQL Server, SQL Tips and Tricks6 commentsOne of the blog reader sent me the below mail. I always the linked server has been created but failed a connection test find that using linked server have been of concern and trouble at a
Cannot Initialize The Datasource Object Of Ole Db Provider Sqlncli11 For Linked Server
number of places. So when the mail landed – well I was not surprised that there was a problem. It sql server error 7399 invalid authorization specification is common and hence the resolution for the same is also common yet not discussed. So I thought this blog will bring the nuances that I thought was the resolution and which
The Ole Db Provider "sqlncli11" For Linked Server Reported An Error. Authentication Failed.
helped our blog reader.Hi Pinal, I have two SQL Server instances on same machine and I want to fetch data from each other. So I went ahead and created linked server. In SQL Server Management Studio, Server Objects > Right-click, Linked Servers, and then selected New linked server. I gave the remote server name as SQL16NODEB\SQL2014 and “Server type” as SQL Server as shown below.While linked server error + 7399 saving I got error as belowTITLE: Microsoft SQL Server Management Studio-------------------- The linked server has been created but failed a connection test. Do you want to keep the linked server? --------------------The OLE DB provider "SQLNCLI11" for linked server "SQL16NODEB\SQL2014" reported an error. Authentication failed.Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "SQL16NODEB\SQL2014". OLE DB provider "SQLNCLI11" for linked server "SQL16NODEB\SQL2014" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)If I hit No, the linked server is not created. If I created Yes, linked server is created, but any query is failing with the same error.What should I do?Thanks,
and as expected, it failed with the same error.OLE DB provider "SQLNCLI11" for linked server "SQL16NODEB\SQL2014" returned message "Invalid authorization specification".Msg 7399, Level 16, State 1, Procedure sp_testlinkedserver, Line 1The OLE DB provider "SQLNCLI11" for linked server "SQL16NODEB\SQL2014" reported an error. Authentication failed.Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "SQL16NODEB\SQL2014".The message "Invalid authorization specification" means that linked server settings
user …” ★★★★★★★★★★★★★★★ SQL Server ConnectivityAugust 10, 200678 Share 0 0 SQL Server 2005: “Login failed for user ‘NT AUTHORITYANONYMOUS LOGON'”.
SQL Server 2000: "LoginOle Db Provider Sqlncli11 For Linked Server Returned Message Invalid Connection String Attribute
failed for user "(null)". Reason: Not associated with a trusted SQL Server connection".
The Test Connection To The Linked Server Failed Error 18456
The errors listed above is very typical when deploy linked server with delegation. They actually are thrown by the linked server the ole db provider sqlncli11 for linked server does not contain the table and pass by middle server to the client application. In this post, I will discuss how to properly configure SQL instances and Windows environment in most common scenario and try to make configuration steps as http://blog.sqlauthority.com/2015/10/03/sql-server-linked-server-creation-error-ole-db-provider-sqlncli11-for-linked-server-returned-message-invalid-authorization-specification/ explicit as possible.
By using delegation in distributed query, such as linked server query, the SQL instance obtains impersonated token of the user logon credential to gain access to resources of another SQL instance, the linked server. In delegation setting, the client connection and linked server object are configured to use integrated authentication in SQL Server’s term as opposed to SQL login. Some time integrated authentication also referred https://blogs.msdn.microsoft.com/sql_protocols/2006/08/10/sql-linked-server-query-failed-with-login-failed-for-user/ as trusted connection or Windows authentication. Linked server login can also use SQL login, but it is not discussed here. To simplify the discussion, let’s assume two SQL Server instances are installed on machine A and B respectively. Also, let’s assume A is the middle server that has a linked server object configured which points to a SQL instance on machine B. If the client is on machine C different from A, we call it double-hop setting; if the client is collocated with middle server machine A, we call it single-hop setting. In single-hop setting, it is relatively straightforward to configure linked server to work. Believe or not, double-hop setting requires more careful configurations as you will see. This is because in single-hop setting, windows NTLM authentication, which is available in most common setting if all machines are windows, is sufficient for delegation; while in double-hop setting, Kerberos authentication is mandate for flowing user’s credential through machine boundaries from the client to the linked server. It requires windows domain, correct DNS name resolution, proper account setting in both Active Directory and SQL Server. To make sure Kerberos delegation [1] is correct becomes vital to operate distributed query with delegation. The authentication scheme required by delegation ihere for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers http://stackoverflow.com/questions/5198381/linking-server-in-sql-server-2008-r2 or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x http://dba.stackexchange.com/questions/104362/getting-invalid-authorization-specification-when-creating-linked-server Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Linking Server in SQL Server 2008 R2 up vote 9 down vote favorite Can anyone guide me around to linking a server to another in SQL Server 2008 R2? I am getting the following linked server error when trying to do so in 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 "SQLNCLI" for linked server "CDSPM1" reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "CDSPM1". OLE DB provider "SQLNCLI" for linked server "CDSPM1" returned message ole db provider "Invalid authorization specification". (Microsoft SQL Server, Error: 7399) sql-server sql-server-2008 linked-server share|improve this question edited Feb 7 '13 at 0:55 abatishchev 57.1k56215354 asked Mar 4 '11 at 19:33 Jeff 2285923 3 What's wrong with a screen shot? –Oded♦ Mar 4 '11 at 19:35 Is the login and password valid for the linked server? –jim31415 Mar 4 '11 at 20:23 add a comment| 6 Answers 6 active oldest votes up vote 7 down vote accepted Seems like Authentication problem. Test by creating the linked Server using "Server Type" as "Sql Server". Then go into "Security" and set your user mapping. As a test, create a SQL login on the remote system and specify that on the "Be made using this security context" Be sure that you can PING the "Linked Server" name first. HTH share|improve this answer answered Mar 5 '11 at 13:09 VDMT 1088 Thanks! It saved my day. Mainly the "Be made using this security context" part. –Ramon Araujo Jul 19 at 5:14 add a comment| up vote 1 down vote Use drop down and choose SQL Server Native Client instead of SQL Server share|improve this answer answered Feb 7 '13 at 0:55 abatishchev 57.1k56215354 Just selecting this worked for me immediately when nothing else did. Thanks! –Matt Wanchap Apr 15 at 1:43 add a comment| up vote 1 down vote Use below link to view the screenshots of connec
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Getting “invalid authorization specification” when creating linked server up vote 1 down vote favorite I have two databases. Database A is on DBServer\Instance1 and Database B is on DBServer\Instance2. I need to create a view inside Database B that looks into a table in Database A. I was told I need to add DBServer\Instance1 as a linked server to DBServer\Instance2 first. That's what I'm trying to do. Just for testing, I created a user account named linked_server_acct and gave it all the roles on both servers (Under Security > Logins). I can log in to each instance using SQL Management Studio, so I know for a fact that the login works, but I'm not able to use it for some reason when creating the linked servers. It fails with "invalid authentication specification - 7399'. I can't think of a reason why an account that is basically a super admin of a server can't authenticate to it. Any help would be appreciated. DBServer\Instance1 linked server script: /****** Object: LinkedServer [DBSERVER\INSTANCE1] Script Date: 06/17/2015 16:17:39 ******/ EXEC master.dbo.sp_addlinkedserver @server = N'DBSERVER\INSTANCE1', @srvproduct=N'SQL Server' /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DBSERVER\INSTANCE1',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DBSERVER\INSTANCE1',@useself=N'True',@locallogin=N'linked_server_acct',@rmtuser=NULL,@rmtpassword=NULL GO EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'collation com