September 26, 2012 at 11:25 am
Built a new Windows 2008 R2 x64 server, and installed SQL Server 2008 on it, as a test version of one we already have in production. (This is a VMWare virtual machine.) Using the default instance only of SQL Server.
Added one login, which is the Domain Admins group as defined in Active Directory. My own Active Directory username is a member of that group. The group has server roles of Public and Server Admin. This is how we try to arrange authentication on all our servers.
Let's call this server AAA. So the SQL Server instance is also of course called AAA.
So, I run Management Studio (while logged in to my own username) and it says my username can't login to the "(local)" instance. It can't even see it when browsing the local machine. When I browse the network, it sees all the other SQL Server instances out there, and can login to them. It even sees AAA listed on the network ... but can't login to it.
Meanwhile if I run Management Studio on machine BBB (under the same username), it can login to AAA, no problem.
Leaving/rejoining the domain changed nothing. Uninstalling/reinstalling SQL Server did nothing. Updating from SP1 to SP3 changed nothing. Changing the client interface between shared memory and TCP/IP (and even enabling named pipes on server and client) didn't help.
Finally, two things worked: (1) Running Management Studio as an administrator. (2) Adding my own username as its own login. Doing either of these things allows me to login locally.
On the original production server (as opposed to this test version) we never have to do (1). We probably had (2) defined in the past (possibly because I couldn't get it to work any other way and was in a big hurry), but that login has since been deleted because we want to authenticate only by the group. (Note the production server currently has multiple groups allowed access, and I'm a member of most or all of them.)
So what could be causing this? Number (1) above has me wondering if I might need to reinstall while logged into the built-in (domain) Administrator account, but hey, I'm a domain admin so I shouldn't have to ... right? I don't think I did that on the original install on the production machine, but can't be sure because it was over a year ago.
September 26, 2012 at 6:52 pm
What is the exact error SSMS is throwing? Do you see anything corresponding to these failed login attempts in Event Viewer?
September 27, 2012 at 11:35 am
AmarettoSlim (9/26/2012)
What is the exact error SSMS is throwing? Do you see anything corresponding to these failed login attempts in Event Viewer?
Well of course there is currently no error when I use my own login (because I added it individually), but I tried logging into a different username, which is also a member of Domain Admins, and got the same symptoms.
Trying to browse to the local server gets nothing but an empty box where normally there is a list of things to connect to.
Explicitly typing a server name of (local) gets this error:
Cannot connect to (local).
Additional information:
Login failed for user 'DOMAIN\user'. (Microsoft SQL Server, Error: 18456)
(I have listed example domain and user names, instead of the real ones I use.)
I hadn't thought of checking the Event Log. In there I find this information when a failed login occurred:
Log Name: Application
Source: MSSQLSERVER
Date: 9/27/2012 12:13:10 PM
Event ID: 18456
Task Category: Logon
Level: Information
Keywords: Classic,Audit Failure
User: DOMAIN\user
Computer: Sqlcctest.domain.com
Description:
Login failed for user 'DOMAIN\user'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="MSSQLSERVER" />
<EventID Qualifiers="49152">18456</EventID>
<Level>0</Level>
<Task>4</Task>
<Keywords>0x90000000000000</Keywords>
<TimeCreated SystemTime="2012-09-27T17:13:10.000000000Z" />
<EventRecordID>4566</EventRecordID>
<Channel>Application</Channel>
<Computer>Sqlcctest.domain.com</Computer>
<Security UserID="S-1-5-21-2736685792-2679476787-71229117-1133" />
</System>
<EventData>
<Data>DOMAIN\user</Data>
<Data> Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.</Data>
<Data> [CLIENT: <local machine>]</Data>
<Binary>184800000E0000000A000000530051004C004300430054004500530054000000070000006D00610073007400650072000000</Binary>
</EventData>
</Event>
I don't know what kind of "infrastructure error" it might be talking about. The client can access other SQL Server machines on the network, and other clients on the network can access this server.
September 28, 2012 at 11:02 pm
Based on the information provided its likely you have UAC enabled on this server. Running SSMS as administrator, aka elevated privileges allows you to connect locally using a member of the domain admin group. Disable UAC and tedt connectivity.
My thought here is that when connecting to the instance remotely SQL is authenticating you but Windows isnt happy.
Now as for explicitly defining a domain account in SQL and connecting locally without running SSMS as administrator versus the domain admin group... Im not sure.
This is 2008 so the BUILTIN Administrators group isnt defined in the server level logins right?
*written on a cellphone excuse any typos
October 2, 2012 at 3:29 pm
AmarettoSlim (9/28/2012)
Based on the information provided its likely you have UAC enabled on this server. Running SSMS as administrator, aka elevated privileges allows you to connect locally using a member of the domain admin group. Disable UAC and test connectivity.
I can look into this, but how would that explain why this server's twin (the production version) also has UAC enabled, but it DOESN'T have this problem? I see the same UAC popup box when I run, say, SQL Server Configuration Manager on both servers. I have to answer Yes on both.
Also, how would that explain why SSMS can't even BROWSE to (local)? (While remote machines CAN browse to it, using the server name.) It would seem browsing shouldn't be related to UAC because this is before a login is even attempted.
This is 2008 so the BUILTIN Administrators group isnt defined in the server level logins right?
Right, the builtin Administrators group isn't listed. The ONLY SQL Server login I added was DOMAIN\Domain Admins (until I tried also adding DOMAIN\Username so I could operate without clicking Run as Administrator).
As another test, I've now deleted DOMAIN\Username as a SQL Server login and the problem has returned. So now the authentication is similar to the twin, which has DOMAIN\Domain Admins and not the username either. The twin succeeds in browsing and logging in; this server doesn't.
After reading and thinking about this, for a minute I thought I had the solution -- maybe DOMAIN\Domain Admins wasn't in the SERVER\Administrators group on Windows. But I checked, and it IS a member of local Administrators on both servers.
My thought here is that when connecting to the instance remotely SQL is authenticating you but Windows isnt happy.
But Windows IS happy when connecting remotely (either outgoing or incoming). I guess I don't understand what you mean by this sentence. Any incoming connection has to go through Windows and SQL both.
----------------
P.S. I just discovered that even if I start SSMS by saying Run as Administrator, even though it can LOGIN to an explicitly defined (local), it STILL cannot BROWSE to the local machine. The browse box shows up empty. But browsing to local works fine on the (production) twin server. So it appears that part of the problem remains even when UAC is taken out of the picture.
(And yes, when I run SQL Server Configuration Manager, it shows SQL Server Browser IS running.)
October 5, 2012 at 10:04 am
OK, I have disabled UAC on the new test server. In fact when I run programs like SQL Server Configuration Manager, they no longer ask for administrator confirmation, which is a convenience.
Now now most things work, including SSMS being able to explicitly connect to (local). However, SSMS still can't BROWSE to (local).
And the original production server still has UAC enabled and it does NOT show any problems. There, SSMS can browse to (local) just fine. So we still have not solved the real question. Something's going on that can't be explained by UAC.
October 5, 2012 at 9:31 pm
Hmm, as for not being able to see remote instances or the local instance is the SQL Server Browser service running on this troubled machine? Firewall settings good? SQL Server Browser uses port 1434 - UDP
Also, check the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Name
I believe the "Local Servers" tab in the Browse for Servers dialog, directly read from here. Compare it to your other machines and see if there is any glaring discrepancies.
I just pulled up an article on SQL Server Browser on MSDN:
http://msdn.microsoft.com/en-us/library/ms165724(v=sql.90).aspx
October 8, 2012 at 3:43 pm
AmarettoSlim (10/5/2012)
Hmm, as for not being able to see remote instances or the local instance
No, that's not right. As I said in my first post, I CAN browse to and connect to ALL remote instances. Remote clients CAN browse to and connect to ME. I just can't browse to (local).
is the SQL Server Browser service running
As I said in my 10/2 post, "And yes, when I run SQL Server Configuration Manager, it shows SQL Server Browser IS running."
Firewall settings good?
Why would the firewall affect local-to-local communication? (Especially since it doesn't affect local-to-remote or remote-to-local?) Besides, as I said in my first post, shared memory (which is the default for local access) and named pipes get the same result as TCP/IP (which defaulted OFF for local access; I had to explicitly enable it for testing).
Also, check the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Name
Both servers have identical values here.
It may be that if we correct the original problem #1 (not being able to even CONNECT to (local) unless either running as admin, or having an explicit user login instead of a group), this problem #2 (not being able to BROWSE locally) might get fixed by the same solution.
The fact that problem #1 goes away if using a single user login vs. a group suggests it may be caused by some sort of failure of SQL Server to properly communicate with Active Directory (that would explain why it doesn't realize that the user is a MEMBER of the group). But how to troubleshoot that? Does looking down that rabbit hole give any clues?
October 8, 2012 at 4:50 pm
This seems similar to a known bug http://connect.microsoft.com/SQLServer/feedback/details/425661 that has not yet been fixed (I think - at least not in SP1, SP2 or SP3), but that was on Win 7.
It is not related to the SQL Browser service as that is not used to connect to or list local services.
The other "twist" is that it works when logged in as Administrator, which has not been mentioned on Connect.
Are you sure your user when logged in has access to the registry keys at
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names ?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);October 9, 2012 at 12:56 pm
mister.magoo (10/8/2012)
This seems similar to a known bug
Hmmm, yes, it certainly does. It's not exactly the same situation, because I'm using a different browse window, but it's close. I'm not working in the Registered Servers window (right-clicking Local Server Groups, then picking Tasks - Register Local Servers); on the contrary I'm working in the Connect Object Explorer (the separate window that comes up automatically when you start up SSMS).
However I did reproduce the exact bug described when trying to Register Local Servers (and I have SP3 installed). Funny -- they say the bug was fixed in Feb. 2011, so why isn't it in SP3 which didn't come out till Oct. 2011?
but that was on Win 7
It's probably not specific to Win 7. Reading the article, it seems to be specific to using the x64 version of SS -- which I am using.
The other "twist" is that it works when logged in as Administrator, which has not been mentioned on Connect.
Well, in my tests I can reproduce the bug described in the article even when UAC is turned off. My Problem #1 gets fixed if you're Administrator, but not problem #2.
Are you sure your user when logged in has access to the registry keys at
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names?
Well, I'm the user in question, and using Regedit I could navigate to that key and display it with no problem.
However recall when when I added my own username explicitly as its own login in SQL Server, then my problem (at least problem #1) went away. I tried adding it back again for a test, but id didn't fix the browsing problem (#2).
Now here's an interesting twist: There are 5 browse-able things: Database Engine, Analysis Services, Reporting Services, Compact Edition Databases, and Integration Services. Even though Database Engine exhibits this bug (the browsing bug, my problem #2), Integration Services DOES NOT. If SSIS is installed, I can use Tasks - Register Local Services to successfully register it.
Also if SSIS is installed and I start the Connect Object Explorer from the icon instead of from the dropdown box, the local window is no longer blank -- it shows all 5 browse-able services, although it still doesn't show a local Database Engine to browse to -- only Integration Services.
In fact I added SSIS to my test server installation to see if that was the thing that made it act differently from the production server (which DOES have SSIS) -- sadly that didn't work.
Bottom line, I see enough similarities between my situation and this bug that I'm going to theorize that my problem (at least problem #2, the browsing) is actually related. Since #2 is minor, and I have a workaround for #1 (disabling UAC), and I have lots of other things to do, I'm going to have to put this on the shelf. But if anyone else has an idea they can certainly add a post, and I'll get an Email.
Many thanks for your post, Mr. Magoo. I now have more of an impression that I can probably safely go to other activities, and it's less likely that this problem will cause undue side effects, or more problems, down the road.
October 9, 2012 at 2:13 pm
One more thing -- it turns out Microsoft suggests disabling UAC in situations such as this, where the only activity on the console is server admin tasks.
http://support.microsoft.com/kb/2526083
I had more or less reached the same conclusion, but this makes me feel better about it!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply