none
is Active Directory - Password a valid authentication mode for determining if our service account has login etc rights RRS feed

  • Question

  • Hi we run 2017 STD. Its sometimes expensive for us to spin up the scheduling tool we have to determine if the service acct we use in that tool has new rights we attempted to give it via addition to an nt group. 

    So as a shortcut we tried logging into the server (via ssms) we thought we just gave it rights to.  And chose Active Directory - Password as the auth mode.  We know the service acct's password.  But we got the error below. 

    Is logging in this way a valid way of testing that the permissions we thought we gave have been accomplished?  Of course we would have selected from target tables once we got further.  If such an "impersonation chain" capability is something that perhaps we don't have but our dba does, how do we ask him if he has it before we waste his time asking him to try it? 

    Thursday, December 12, 2019 4:29 PM

Answers

  • If you want to test the permissions inside SQL Server, you can do:

    EXECUTE AS LOGIN = 'DOMAIN\user'
    go
    -- Do what you want to test here
    go
    REVERT   -- This commands restores your original context.

    You need to be logged in as sysadmin to do this. Or to be precise, you needw to have IMPERSONATE permission on the login in question.

    If you only want to test database permissions, and you only have db_owner rights, you can use EXECUTE AS USER instead, but now you will be sandboxed into the current database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by db042190 Friday, December 13, 2019 1:39 PM
    Thursday, December 12, 2019 10:06 PM

All replies

  • Hi db042190,

    If you want to connect to a sql server with a different windows account you will have to open the management studio on the client that you are trying to access with a different login. you will have to search the management studio.exe file location, then right click (if you don't see the option you will have to press control shift and then right click). Active directoy password is for azure sql db not the onprem -> https://docs.microsoft.com/en-us/sql/ssms/f1-help/connect-to-server-database-engine?view=sql-server-ver15

    I hope this helps.

    regards!

    Thursday, December 12, 2019 4:41 PM
  • thx, we are on prem. so I think you are saying forget about Active Directory - Password.

    ok, on my dev client I rt clicked the exe and I also ctrl/shift/rt clicked but don't see anything that suggests running ssms as someone else.  If I rt click the ssms icon I see run as admin but am not sure that is what you are going for.

    I am rdp'd into the client as myself.  and haven't tried but would guess that the service acct isn't allowed to rdp there.  and would guess it doesn't have exec permission anywhere on that client.


    • Edited by db042190 Thursday, December 12, 2019 5:09 PM more info
    Thursday, December 12, 2019 4:55 PM
  • thx, we are getting this error.  I wonder if that's related to that service (browser?) that acts as a go between other sql things.


    • Edited by db042190 Thursday, December 12, 2019 6:20 PM name of suspected service
    Thursday, December 12, 2019 6:17 PM
  • Is the account that you are trying to use admin on the client machine??
    Thursday, December 12, 2019 7:05 PM
  • no..
    Thursday, December 12, 2019 7:09 PM
  • Add the account to admin group and test it again
    Thursday, December 12, 2019 7:36 PM
  • thx Gonzalo, cant do that.

    But just for understanding what service couldn't be started?

    Thursday, December 12, 2019 7:50 PM
  • Try adding it to admin group and test the execution again.
    Thursday, December 12, 2019 8:12 PM
  • If you want to test the permissions inside SQL Server, you can do:

    EXECUTE AS LOGIN = 'DOMAIN\user'
    go
    -- Do what you want to test here
    go
    REVERT   -- This commands restores your original context.

    You need to be logged in as sysadmin to do this. Or to be precise, you needw to have IMPERSONATE permission on the login in question.

    If you only want to test database permissions, and you only have db_owner rights, you can use EXECUTE AS USER instead, but now you will be sandboxed into the current database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by db042190 Friday, December 13, 2019 1:39 PM
    Thursday, December 12, 2019 10:06 PM
  • Hi db042190,

    >>we are on prem. so I think you are saying forget about Active Directory - Password.

    What authentication mode do you use now? Windows Authentication or SQL Server Authentication?

    In addition, please make sure SQL Server services are running in SQL Server Configuration Manager.

    And you could try to click the options button on your connection window above and add TrustServerCertificate=True under Additional Connection Parameters.

    For more details, please refer to the following similar threads:

    The certificate chain was issued by an authority that is not trusted

    Connect to SQL Server with Windows Authentication in a different domain

    Hope this could help you.

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 13, 2019 6:53 AM
  • thx Erland, if the acct is part of a login group that is listed as a login (rather than acct explicitly listed as a login), should this test also be valid?  I'll ask my dba to run one or the other as admin because I don't have admin or db_owner there. 
    • Edited by db042190 Friday, December 13, 2019 12:34 PM clarity
    Friday, December 13, 2019 12:32 PM
  • thx Amelia, we use windows auth.  and i know for sure the services are running because I myself can connect to the target db's and run a select.

    because someone else has to enter the pswd every time I try a different idea here, i'm reluctant to try yours only because it sounds like your first link has to do with trust between the sql server and my client.  But I believe the error i'm getting has more to do with the service acct's permissions on the client machine where ssms is being run.   and because i'm in the same domain as the acct i'm reluctant to open your 2nd link.  and remember the 1st error came from an attempt to use an azure feature in a non azure environment.  so i think anything having to do with certificate chain is off the table for now.

    Friday, December 13, 2019 12:58 PM
  • looks like even if its part of the group (ie not explicitly listed) which i think you call the "principal", erlands answer works. we ran as login.
    Friday, December 13, 2019 1:39 PM
  • i added an MS wishlist item called "dmv for login and user permissions".  its being processed so i guess even if it passes review it will be a while before you can see it. 
    Friday, December 13, 2019 2:32 PM
  • thx Erland, if the acct is part of a login group that is listed as a login (rather than acct explicitly listed as a login), should this test also be valid?  I'll ask my dba to run one or the other as admin because I don't have admin or db_owner there. 
    Yes, as you have found out, you can impersonate an account, even if it only has access through a Windows group.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, December 13, 2019 9:21 PM
  • i added an MS wishlist item called "dmv for login and user permissions".  its being processed so i guess even if it passes review it will be a while before you can see it. 

    There are already quite a few options in this area.

    sys.user_token gives you all your tokens inside the database, this includes database roles. sys.login_token gives you the same on server level. You will also see Windows groups you are user of.

    fn_my_permissions() lists permission on a certain level. See Books Online for details on the arguments. fn_my_permissions(NULL, NULL) gives your server-level permissions.

    has_perms_by_name() permits you test for a specific permissions.

    And together with EXECUTE AS you can investigate the permissions for another account.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, December 13, 2019 9:25 PM
  • thx, i'd like to see it for others and not need sa or db_owner to see it.
    Monday, December 16, 2019 12:18 PM
  • thx, i'd like to see it for others and not need sa or db_owner to see it.

    Well, to be able to see someone else's permissions, you would need to have permission to see that information. Which with my suggests above means that you need IMPERSONATE on the login (to see server-level information)  or on the user (to see DB-level information). Since these are elevated permissions, you would typically have CONTROL permission on server or database. (Which you have if you are sysadmin or db_owner.)

    Some new DMVs could be introduced, but unless they introduce any new permissions, you would still need elevated permissions to see the information. And the new permissions would also be considered elevated. That is, they would not be something which is handed out casually.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, December 16, 2019 4:44 PM
  • thx, I only got 1 vote (which is mine) so i'm not holding my breath!
    Monday, December 16, 2019 6:51 PM