January 30, 2012 at 2:32 pm
I have been trying to add linked server. But I get the following error
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 "SQLNCLI" for linked server "FTWP1IEGBOV01\GBOSQL1" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "FTWP1IEGBOV01\GBOSQL1".
OLE DB provider "SQLNCLI" for linked server "FTWP1IEGBOV01\GBOSQL1" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476
Can any one please help me why this is happening?
January 30, 2012 at 2:40 pm
in 2008r2...sorry, havent got 2005 anymore...when creating the linked server there is a "security tab" that allows you you to specify the login to the remote server...have you checked this out?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 30, 2012 at 2:56 pm
J Livingston SQL (1/30/2012)
in 2008r2...sorry, havent got 2005 anymore...when creating the linked server there is a "security tab" that allows you you to specify the login to the remote server...have you checked this out?
I can confirm that the form is identical in 2005.
na1774, set your security settings and you should be fine.
-- Gianluca Sartori
January 30, 2012 at 3:03 pm
Thank for the reply. I did add a remote user in the SECURITY tab. In the SECURITY tab for local user I gave the username which has sysadmin server role(I was creating the linked server logged in with the same account). In the Remote User I gave the name of the remote user which I manually created at the destination(i.e. I mapped the local user say 'X' which has sysadmin server role with the remote user say 'X' which is login at the destination with sysadmin server role). Even the remote user also has sysadmin server role. Now I came back to the source server and tried running a query on the remote server. For Example
select * from [FTWP1IEGBOV01\GBOSQL1].MVDB.dbo.ab
{**[FTWP1IEGBOV01\GBOSQL1] is the linked server instance name}
I get the following error.
OLE DB provider "SQLNCLI" for linked server "FTWP1IEGBOV01\GBOSQL1" returned message "Invalid authorization specification".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "FTWP1IEGBOV01\GBOSQL1" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "FTWP1IEGBOV01\GBOSQL1".
Why does it say that at the destination link server authorization has failed? When the destination user I mapped while creating the Linked server has sysadmin server role?
Any Ideas?
January 30, 2012 at 3:16 pm
further down the "security tab" page are other options
have you tried selecting radio button for "be made using this security context" and entering the the remote login details?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 30, 2012 at 3:47 pm
Thank guys Its Solved. This Web Site Is An Asset.
January 30, 2012 at 9:18 pm
I have added some servers successfully But I have a problem adding one particular server. The error message I get is this
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)
------------------------------
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
OLE DB provider "SQLNCLI" for linked server "FTWP1IEBVWV01\BILLING" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "FTWP1IEBVWV01\BILLING" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". (Microsoft SQL Server, Error: 65535)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=65535&LinkId=20476
------------------------------
BUTTONS:
&Yes
&No
------------------------------
Any Ideas what might be causig this?
January 31, 2012 at 12:55 am
na1774 (1/30/2012)
...this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections."...
Enable remote connection to this server.
By default remote connection is disabled.
January 31, 2012 at 7:14 am
Hi Thank for the reply.....
I went to the Surface Area Config Manage----->Remote Connections --->the option selected is "Using Both TCP/IP and named pipes".
Even Then I am still not able to add that server as Linked server from source machine. I get the same error as mentioned above.
Can u help what might be causing this?
January 31, 2012 at 7:32 am
Sorry if this has already been stated but what user are you using (AD or SQL)?
Also where are you trying to create the LS (on the server or remotely)?
Are you able to RDP to the server to create locally?
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 31, 2012 at 7:52 am
Hi Adam,
Thanks for the reply
The user I am using is a logged in using a local sql user(with sysadmin server role) on the server from where I am creating the linked server.
The User I am mapping to on remote server is also a sql user with sysadmin server role.
The LS I am creating is not a local instance but is on a remote server. To make thing clear.
I am connected to the server X which has sql server instance X\I. I am trying to creating a linked server on X\I. The LS is on machine Y and its instance name is Y\I. When I try to add Y\I on X\I. I get the above error. So I went to the Surface Area Config on Y machine and looked under remote connection option. The selected option was "Using Both TCP/IP and named pipes". Even then I am getting the error on X.
I am not very clear abt ur last question -- "Are you able to RDP to the server to create locally? ".
If RDP means "Remote Desktop". No I am connecting to the remote machine. Instead I am trying to create the linked server in the machine X(Which I am logged on ) in X\I's management studio.
January 31, 2012 at 8:06 am
For the ease of troubleshooting (and since you are using SQL logins anyway) can you please use (if you have it) or dl from MS if you havent SP_HELP_REVLOGIN (specific for the versions you are using).
Use this to export the SQL user with hashed password from X to Y. Then try to create the LS's using this same user. Or use the code below on each server to create 'test_account' login with password 'test'
CREATE LOGIN [test_account] WITH PASSWORD = 0x0100C2C35E2E166DF31651FA5C2B7D47F84DA5B9AF2AC535E556 HASHED, SID = 0x6D3A3AC7BC9539479EF77E90A025FD12, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
The purpose of this is that the login will have the same name and sid and rights on both servers. If that doesnt work there is something more sinester going on
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 31, 2012 at 8:59 am
Thanks for the reply dude. I will try it and get back to U if I face any Issues.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply