May 27, 2014 at 11:04 am
As a database developer, I have so many databases that I "own" scattered across various servers that it''s getting difficult to remember where all of my databases reside. It doesn't help that the DBAs have taken to some very hard to remember server naming conventions.
I was going to create aliases via the configuration manager, but it turns out the DBAs overwrite my entries each night with THEIR aliases and they won't add any for my use.
So I decided to simply add some records to my host file so that instead of having to connect to "SERVER-AD_DADF-DAFDASS" I can just use "CustomerA".
This solution seems to work until I tried to access a linked server. If I connect to the main server via it's actual name, I can hit the remote/linked server with no issue. However, if I connect to the main server using it's alias, connection to the remote/linked server fails with: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
The linked server is set up to use current security context and I'm not logging into the alias any differently that I do when using the actual server name.
My DBAs are not able to assist in resolving this. Any ideas?
Thanks in advance.
May 27, 2014 at 12:56 pm
So I did some more research on this and was following the steps on a website that walked me through things to check that could point as to why this was occurring.
In the middle of testing, it suddenly started working just fine without my having changed anything.
Very odd.
May 28, 2014 at 2:34 pm
I also develop databases across multiple servers, via linked server and open query. So I read any articles that involve linked servers (this is my favorite as well). So if you figure out any additional information, I am interested in hearing about it.
Thanks I appreciate it.
Cheers!
The pain of Discipline is far better than the pain of Regret!
May 28, 2014 at 3:00 pm
bvarnell 55573 (5/27/2014)
So I did some more research on this and was following the steps on a website that walked me through things to check that could point as to why this was occurring.In the middle of testing, it suddenly started working just fine without my having changed anything.
Very odd.
This is a classic sign of to many cooks in the kitchen...just saying. Glad you got it working
MCSE SQL Server 2012\2014\2016
May 28, 2014 at 3:13 pm
Ok, the issue has cropped up again so I have more info.
For ease of telling the story, "ServerA" is when I connect to the server by name and "ServerB" is when I connect to the server using the Alias I created in my local host file. And "the view" is a view that queries a table on the linked server.
So here is what I found this morning:
1) Connect to serverB
2) Query the view
3) Query fails
4) Connect to ServerA
5) query the view
6) Query successful
7) Retry view on ServerB
8) Query successful
9) Go back about 15 minutes later and try view on ServerB
10) Query fails
WTF?
May 28, 2014 at 3:19 pm
I am sorry to hear the problem has reared it ugly head. Can you provide an error message(s) you are getting from SSMS when the query fails, please?
Could this be a possible database not selected correctly?
Can you review the query on the view or that creates the view?
The pain of Discipline is far better than the pain of Regret!
May 28, 2014 at 3:22 pm
SQLArnold (5/28/2014)
I am sorry to hear the problem has reared it ugly head. Can you provide an error message(s) you are getting from SSMS when the query fails, please?Could this be a possible database not selected correctly?
Can you review the query on the view or that creates the view?
I detailed the error message in my first post:
NT AUTHORITY\ANONYMOUS LOGON
It's not an issue with the view. As stated, I works fine when connected to the main server via the actual server name (i.e. non-alias name)
May 28, 2014 at 7:40 pm
bvarnell 55573 (5/28/2014)
Ok, the issue has cropped up again so I have more info.For ease of telling the story, "ServerA" is when I connect to the server by name and "ServerB" is when I connect to the server using the Alias I created in my local host file. And "the view" is a view that queries a table on the linked server.
So here is what I found this morning:
1) Connect to serverB
2) Query the view
3) Query fails
4) Connect to ServerA
5) query the view
6) Query successful
7) Retry view on ServerB
8) Query successful
9) Go back about 15 minutes later and try view on ServerB
10) Query fails
WTF?
Crap. I saw the fix for this on the web a couple of days ago and forgot to save the bloody link. I don't know if I can find it but I did want you to know that there's a fix out there and to not give up looking.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 28, 2014 at 7:45 pm
Jeff Moden (5/28/2014)
Crap. I saw the fix for this on the web a couple of days ago and forgot to save the bloody link. I don't know if I can find it but I did want you to know that there's a fix out there and to not give up looking.
LOL. Good to know I'm not the only unlucky SOB to run into this.
My Google-Fu hasn't turned up much thus far.
May 29, 2014 at 2:30 pm
Okay, Well I am sorry I was unable to help. Maybe next time. I wish you the best of luck with the Google-Fu, maybe roll the dice on Bing-Fu? 😛
The pain of Discipline is far better than the pain of Regret!
May 29, 2014 at 2:41 pm
SQLArnold (5/29/2014)
Okay, Well I am sorry I was unable to help. Maybe next time. I wish you the best of luck with the Google-Fu, maybe roll the dice on Bing-Fu? 😛
No worries. I'll get is sorted eventually....when time permits.
May 30, 2014 at 6:10 am
Jeff Moden (5/28/2014)
bvarnell 55573 (5/28/2014)
Ok, the issue has cropped up again so I have more info.For ease of telling the story, "ServerA" is when I connect to the server by name and "ServerB" is when I connect to the server using the Alias I created in my local host file. And "the view" is a view that queries a table on the linked server.
So here is what I found this morning:
1) Connect to serverB
2) Query the view
3) Query fails
4) Connect to ServerA
5) query the view
6) Query successful
7) Retry view on ServerB
8) Query successful
9) Go back about 15 minutes later and try view on ServerB
10) Query fails
WTF?
Crap. I saw the fix for this on the web a couple of days ago and forgot to save the bloody link. I don't know if I can find it but I did want you to know that there's a fix out there and to not give up looking.
This looks pretty similar and would explain why it worked on the second attempt:
http://datarealized.wordpress.com/2010/07/14/kerberos-sql-server-2008-windows-server-2008/
In short, the connection to ServerA in-between may be giving you the Kerberos "ticket" that then enables it to work on try number 2, so double-hop simply may not be working at all (probably due to the hostname fudging).
May 30, 2014 at 6:16 am
In regards to the original issue, are you just doing this so they have logical names when browsing in SSMS? If so, you can use the Registered Servers functionality and override the name with a friendly name, which then shows up in the Object Explorer - unfortunately this doesn't help with external applications.
Really the only way to do this properly is through a SQL Server Alias (through SQL Server Configuration Manager).
May 30, 2014 at 12:17 pm
HowardW (5/30/2014)
In regards to the original issue, are you just doing this so they have logical names when browsing in SSMS? If so, you can use the Registered Servers functionality and override the name with a friendly name, which then shows up in the Object Explorer - unfortunately this doesn't help with external applications.Really the only way to do this properly is through a SQL Server Alias (through SQL Server Configuration Manager).
Wha? You can do this? I swear this is something I had thought about at one point but I never found anything saying it could be done.
I'll just do that. Awesome! and Thanks!!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply