March 9, 2016 at 12:36 am
Hi,
for testing/development purpose I created a linked server to the same sql server instance.
The sql server is a SQL2012 SP3 standard instance.
This works fine using the setting 'Be made using the logins's current security context'
But when I change it into 'Be made using this security context' and supply a valid sql server login and password I get the error
Msg 18456, Level 14, State 1, Line 20
Login failed for user 'TestReadonlyUser'.
OLE DB provider "SQLNCLI11" for linked server "TestLinkedServer" returned message "Invalid connection string attribute".
The login is defined as a sql login which has only access to 1 database and belongs to the db_datareader role in this database, is tested and works fine.
I already created the linked server using sp_addlinkedserver with or without specification of the initial catalog in the connection string
Does anyone has an ideal ?
Thx
March 10, 2016 at 6:31 am
The login is defined and set up with permissions on both servers (local and remote)?
And it uses the same password on both servers?
EDIT: On the General tab, what is the Server Type? If "Other", what Provider and Product Name are you using? Is there a Provider String filled out?
March 10, 2016 at 6:41 am
The error message returned to a user whose login has failed is, by design, not very detailed. You can find out more about the reason for the login failure by looking in the errorlog. What does it say in your case?
John
March 10, 2016 at 10:57 am
Are the linked server and the login/user security both setup for the same database?
March 10, 2016 at 11:27 am
Jon.Morisi (3/10/2016)
Are the linked server and the login/user security both setup for the same database?
Linked Servers don't require a database level definition. Only a server-level definition.
But if the OP has defined a database in the linked server, then this is a good question.
March 10, 2016 at 11:48 am
Hi,
The remote user is a sql login which has then necessary rights on the database.
I can login with that user on the server and query the tables.
I use the code below to add the linked server
EXEC master.dbo.sp_addlinkedserver
@server = N'SomeServer',
@srvproduct=N'',
@provider=N'SQLNCLI',
@provstr=N'DRIVER={SQL Server};Server=ServerName; Initial Catalog=[MyTestDB]';
GO
When I use the linked server (I am logged in with an NT-account) everything works fine.
But after I specify to use the sql-login for the remote login (code below), I get the error.
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SomeServer', @locallogin = NULL , @useself = N'False', @rmtuser = N'SomeUser', @rmtpassword = N'Password'
GO
thx for the feedback
March 10, 2016 at 11:54 am
Hi,
I forgot in the previous reply to mention the error from the errorlog
Login failed for user 'SomeUser'. Reason: Could not find a login matching the name provided
Thx for the feedback
March 10, 2016 at 12:08 pm
looks right to me.
check out the workaround in this old article:
March 10, 2016 at 12:09 pm
franky.Piferoen (3/10/2016)
Hi,I forgot in the previous reply to mention the error from the errorlog
Login failed for user 'SomeUser'. Reason: Could not find a login matching the name provided
Thx for the feedback
Orphaned user on the remote side?
March 10, 2016 at 12:50 pm
franky.Piferoen (3/10/2016)
Hi,The remote user is a sql login which has then necessary rights on the database.
I can login with that user on the server and query the tables.
I use the code below to add the linked server
EXEC master.dbo.sp_addlinkedserver
@server = N'SomeServer',
@srvproduct=N'',
@provider=N'SQLNCLI',
@provstr=N'DRIVER={SQL Server};Server=ServerName; Initial Catalog=[MyTestDB]';
GO
When I use the linked server (I am logged in with an NT-account) everything works fine.
But after I specify to use the sql-login for the remote login (code below), I get the error.
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SomeServer', @locallogin = NULL , @useself = N'False', @rmtuser = N'SomeUser', @rmtpassword = N'Password'
GO
thx for the feedback
For SQL Native Client, the Drive should be SQLServer (all one word), without spaces.
But since all other things are equal... I agree with Jon that you have an issue on the other side. Delete the login on the database level, then delete it on the server level (both on the remote server). Then recreate the user on the server level and make sure to open up the User Mapping tab and click it there and grant a role in that dialogue box.
Test it again. If you're still getting a failure, then the code is trying to run something that login can't do. Possibly you have a db_datareader trying to execute a stored procedure or something.
EDIT: Fixed typo
March 10, 2016 at 7:33 pm
Hi,
Remark: I am trying to make a linked server to the same sql server.
The sql server is the default instance, so if the server name is A, I am making a linked server to server A but call it SomeServer.
The remote login belongs to the db_datareader role and I just use a select. Which works fine if I connect directly to the server using the same sql login/password as I define to be used for the remote connection of the linked server.
strange thing is that the linked server works fine until I specify the remote login.
(so as long as I use the option 'be made using the login's currenct security context' everything works fine)
I tried to replace 'SQL Server' by 'SQLServer' in the connection string, but this does not make a difference.
March 11, 2016 at 1:40 am
franky.Piferoen (3/10/2016)
Hi,I forgot in the previous reply to mention the error from the errorlog
Login failed for user 'SomeUser'. Reason: Could not find a login matching the name provided
Thx for the feedback
That suggests the login doesn't exist. What does this return?
SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE name = 'SomeUser'
John
March 11, 2016 at 5:00 am
Hi,
this returns
SomeUserSQL_LOGIN0
March 11, 2016 at 5:06 am
franky.Piferoen (3/11/2016)
Hi,this returns
SomeUserSQL_LOGIN0
Are you sure, absolutely positive, that the user name is spelled exactly the same way on the server as it is in the linked server?
March 11, 2016 at 5:10 am
I am absolutely sure, I've tried and tested it already many times
I've lost my vpn connectione, so I cannot try it againg.
Monday, when I am at the office I will recreate the used login again and test it again
Regards,
Franky
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply