I have a couple clustered SQL server instances, 2 on 2014 and 1 on 2008r2. In all 3 cases, I've created a SQL login (server principal) for a domain group, say DOMAIN\SqlAdmins. I grant it the 'sysadmin' fixed server role, for example. (Don't give me a security lecture, that's not the question!)

Now, on my workstation, using my domain account, which is a member of this group, I fire up SSMS. I can log in and have the rights expected.

However. I RDP to the server that's hosting one of these instances, using this same domain acct (which is a domain admin, in this example). If I use SSMS on the server box in this fashion, and try logging in to the instance with Windows auth, it FAILS.

What piece of this puzzle is out of place here?

  • I have seen this behavior as well, and I always thought it was a security feature on the server... except perhaps during pre-deployment or special (not regular) maintenance periods, you shouldn't be RDP-ing to your DB server! Especially when it's so easy to make the connection from your local workstation. – Joel Coel Mar 9 '16 at 19:34
  • Point taken, for sure. Just wondering why. It may indeed be a security protocol and if that's the case, it's easy to tell the SysAdmin that. – NateJ Mar 9 '16 at 19:38

You might need to log out of your PC and back in.

Group membership is only updated on logon. So if you created the group and immediately tested from your PC, your group membership will not have updated.

Further information: https://msdn.microsoft.com/en-us/library/bb727067.aspx

| improve this answer | |
  • Valid suggestion; unfortunately no, that's not it, as I tried logging off and on several times. Again, it's not on my own PC where it fails; it's on the server itself, running ssms from the same box as the SQL instance is running on. – NateJ Mar 2 '16 at 2:18

This isn't really an "answer", but just in case it's useful to anybody else, the old "right-click & Run-As-Admin" trick worked to make SSMS connect to the on-box SQL instance with Windows-Auth using the account that I was logged in as. Still doesn't bring me any closer to understanding WHY, nor does it help the SysAdmin who only needs to fire up SSMS once-in-a-blue-moon and thus prefers to do it on the SQL Server QA box instead of his own.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.