Your question is quite confused, first you have 2 servers
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
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
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:
@server = N'serverB',
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