December 19, 2017 at 9:43 am
One of the user who has DB owner rights on the database(dbs-sqlprod4), trying to access one of the view from the linked servers (dbs-sqlprod5) gets this error. If he runs it when is on prod4, then it works just fine.
However, I don't run into any issues since I have SA rights on prod4.
Select * from [dbs-sqlprod4].Farmreach.dm.ProfarmerNewsletter
Where FJID in
( 4147071000)
Msg 18456, Level 14, State 1, Line 13
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
December 19, 2017 at 10:40 am
NewBornDBA2017 - Tuesday, December 19, 2017 9:43 AMOne of the user who has DB owner rights on the database(dbs-sqlprod4), trying to access one of the view from the linked servers (dbs-sqlprod5) gets this error. If he runs it when is on prod4, then it works just fine.
However, I don't run into any issues since I have SA rights on prod4.
Select * from [dbs-sqlprod4].Farmreach.dm.ProfarmerNewsletter
Where FJID in
( 4147071000)
Msg 18456, Level 14, State 1, Line 13
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
You get that error when no credentials are passed to the other server. What is setup for the login mappings for the linked server?
Sue
December 20, 2017 at 5:14 am
NewBornDBA2017 - Tuesday, December 19, 2017 9:43 AMOne of the user who has DB owner rights on the database(dbs-sqlprod4), trying to access one of the view from the linked servers (dbs-sqlprod5) gets this error. If he runs it when is on prod4, then it works just fine.
However, I don't run into any issues since I have SA rights on prod4.
Select * from [dbs-sqlprod4].Farmreach.dm.ProfarmerNewsletter
Where FJID in
( 4147071000)
Msg 18456, Level 14, State 1, Line 13
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Check the SPNs are registered and registered correctly for the instances
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 20, 2017 at 9:17 am
He was logged into his own computer so his personal windows credentials were getting passed (laptop-hfgdjhg/ssmith) so no wonder he was getting that error.
I asked him to make RDP conenction and he didn't have any issues once he was logged on to the server using his domain acct.
December 20, 2017 at 9:35 am
NewBornDBA2017 - Wednesday, December 20, 2017 9:17 AMHe was logged into his own computer so his personal windows credentials were getting passed (laptop-hfgdjhg/ssmith) so no wonder he was getting that error.
I asked him to make RDP conenction and he didn't have any issues once he was logged on to the server using his domain acct.
So you had the triple hop error, as Perry detailed for that fix you need to get the SPN's setup correctly to allow Kerberos to pass the tokens through the multiple machines.
Me being paranoid I would look at fixing that than RDP rights as I like to lock down those rights to privileged people only.
December 20, 2017 at 10:36 am
NewBornDBA2017 - Wednesday, December 20, 2017 9:17 AMHe was logged into his own computer so his personal windows credentials were getting passed (laptop-hfgdjhg/ssmith) so no wonder he was getting that error.
I asked him to make RDP conenction and he didn't have any issues once he was logged on to the server using his domain acct.
So when it worked for you and you had no issues, you must have been using a SQL login.
Sue
December 20, 2017 at 11:27 am
Sue_H - Wednesday, December 20, 2017 10:36 AMSo when it worked for you and you had no issues, you must have been using a SQL login.
Sue
SQL Login? what makes you say that? I was using Windows authentication.
Also, I just want to make sure that there is no confusion. We used to have an employee who would use SSMS to login, now he is no longer working for us but does some consulting work for the company. He doesn't have company's laptop anymore so he is asking if his machine name ( 'LAPTOP-AJ0F871P\greg’) can be added into the company's AD so he can use his personal laptop to access our DBs using windows authentication. Is that even possible?
December 20, 2017 at 11:42 am
NewBornDBA2017 - Wednesday, December 20, 2017 11:27 AMSue_H - Wednesday, December 20, 2017 10:36 AMSo when it worked for you and you had no issues, you must have been using a SQL login.Sue
SQL Login? what makes you say that? I was using Windows authentication.
Also, I just want to make sure that there is no confusion. We used to have an employee who would use SSMS to login, now he is no longer working for us but does some consulting work for the company. He doesn't have company's laptop anymore so he is asking if his machine name ( 'LAPTOP-AJ0F871P\greg’) can be added into the company's AD so he can use his personal laptop to access our DBs using windows authentication. Is that even possible?
Because if it's an issue of SPNs for the service account, you would have had the double hop issue as well.
That's why I was asking about the security mapping because you had reported you had no errors and it worked fine. The other non-sysadmin user had the issues.
Sue
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply