0

I want to connect 2 database which are on 2 different server.

SV1 -> SQL Authentication 
SV2\SQLSERVER -> Windows Authentication

LinkedServer query

EXEC master.dbo.sp_addlinkedserver 
@server = N'serverB', 
@srvproduct=N'', 
@provider=N'SQLNCLI', 
@datasrc=N'serverB', 
@catalog=N'MyDatabase',
@provstr='Integrated Security=SSPI;'

EXEC sp_addlinkedsrvlogin 'CGCEL-PVQA\SQLSERVER', true 

But I am getting a login error which executing SELECT query. This is the error which I am getting:

Login failed for user 'sqladmin'.
OLE DB provider "SQLNCLI11" for linked server "SV2\SQLSERVER" returned message "Invalid connection string attribute".

I am new to this, please someone guide me. PS: Lets assume my login credentials for SV1 are: Username : admin Password : admin123

  • Does sqladmin exist in both servers? – Russ960 Sep 19 '17 at 4:12
  • @Russ960 How can sqladmin exist on a server configured to use Windows Authentication only? I.e. it can even exists, but by no means it can log in – sepupic Sep 19 '17 at 6:33
  • @sepupic I think I misread the error message. I meant the windows login would still need to exist in SQL Server. However you are right this is more related to a local login. – Russ960 Sep 19 '17 at 12:44
  • @Russ960 The fact here is that his local login is SQL Server login, not Windows login, so even if 1000 Windows logins exist on the second server, this SQL Server login cannot be mapped to none of them. Just because it's not supported. – sepupic Sep 19 '17 at 12:51
  • @sepuipic I said I mis-read the error message. Sorry! – Russ960 Sep 19 '17 at 13:49
0

Your question is quite confused, first you have 2 servers SV1, SV2, then you link ServerB, but you add login to be used to CGCEL-PVQA\SQLSERVER server. So what does all this mean, that the local server is SV1 and you link SV2=ServerB=CGCEL-PVQA\SQLSERVER to it???

From this one:

Lets assume my login credentials for SV1 are: Username : admin Password : admin123

I see that your local login is admin that is not Windows login but SQL Server login, and in this code:

EXEC sp_addlinkedsrvlogin 'CGCEL-PVQA\SQLSERVER', true

you told the server that the connection will be made using the login's security context, so what did you want to say by adding @provstr='Integrated Security=SSPI;'?

Sorry if I misunderstood something, but from your code I can make another one that is working code but I'm not quite sure it's what you want:

EXEC master.dbo.sp_addlinkedserver 
@server = N'serverB', 
@srvproduct=N'blabla', 
@provider=N'SQLNCLI', 
@datasrc=N'CGCEL-PVQA\SQLSERVER', 
@catalog=N'MyDatabase';

EXEC sp_addlinkedsrvlogin 'CGCEL-PVQA\SQLSERVER', true;

This code creates linked server with the name serverB, it points to the server CGCEL-PVQA\SQLSERVER, and all the logins will use their security context.

This one will not give you the error about

Invalid connection string attribute

But your login admin will fail anyway. This is because your SV2 is configured for Windows Authentication only. This means all SQL Server logins will fail until you change it to Mixed mode and map your SQL Server logins to it.

For remote server configured with Windows Authentication only all the logins from local server can be mapped only to themselves.

So there is no way that your local login admin can logged in to the remote server configured with Windows Authentiction only

  • Your answer clears all my doubts. But I have just one question, whats is value of Local Login and Remote User? – Wocugon Sep 19 '17 at 10:34
  • @The sK Do you mean parameters of sp_addlinkedsrvlogin? In your case they are all NULL. There can be other situations when you want to map all the logins to only one SQL Server login, or when you want to give the access to linked server only to some logins, in these cases you use @locallogin and @rmtuser. For example if you want to link you SV2 to SV1 you can map all your Windows logins to your 'admin' login on SV1, in this case you use sp_addlinkedsrvlogin @rmtsrvname=N'SV1',@useself=N'False',@locallogin=NULL,@rmtuser=N'admin',@rmtpassword='admin123' – sepupic Sep 19 '17 at 11:03
  • In the example above @locallogin=NULL means "all logins from server SV2". If, for example, you wanted to map only the admin of SV1 to your admin on SV1 you could use sp_addlinkedsrvlogin @rmtsrvname=N'SV1',@useself=N'False',@locallogin='myDom\myAdminAcc',@rmtus‌er=N'admin',@rmtpass‌​word='admin123', and if you don't map any other login only myDom\myAdminAcc will be able to use remote server SV1 – sepupic Sep 19 '17 at 11:08
  • Nice explanation. Thank you. all queries cleared :) – Wocugon Sep 19 '17 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.