Post reply

Removing the Linked Server 2 hop Limitation

  • December 18, 2013 at 7:58 am

    #1674439

    That is the problem - it does not exist on the OU level and I would very much like it to 🙂

    We have quite a few Service accounts and automated SQL Server installation/service account creation with the exception of SPN rights. So it would be very convenient for us if this could be set on OU level or if someone has chosen a different approach I would be very interested in hearing which.

    I guess we will have to set the rights on creation of the user object, it would have been soo convenient if it was possible to set it on the OU level 😀

    Thnx for your time!

  • December 18, 2013 at 8:28 am

    #1674463

    Slevin (12/18/2013)

    rstone (12/17/2013)

    I think I was confusing delegation with impersonation. As a sysadmin, I sometimes use EXECUTE AS to impersonate a login to test permissions. In this case, once the application is using sysadmin via script, I suppose it can also impersonate a login. Would the delegation setting affect this? I have never tried to impersonate an account in the domain admin group. Does the delegation setting relate?

    Also, our service accounts are not in the domain admin group.

    I have not noticed any difference in impersonation, but I did not test that out. If I understand correctly, you would been a login of a domain admin who is not a sysadmin on your SQL server. You would then take your login who is a sysadmin and impersonate the domain admin's account. That should work, but I do not know to know extent. For instance, can you use xp_cmdshell to gain access to another server while impersonating this domain admin account? Awe...now I wish I had time to play!

    Derek

    No. xp_cmdshell runs either in the context of the SQL Server service account or the configured proxy account (if access to xp_cmdshell is granted to a non sysadmin user and the proxy account is configured).

    K. Brian Kelley
    @kbriankelley

  • December 18, 2013 at 8:32 am

    #1674467

    patboner (12/18/2013)

    That is the problem - it does not exist on the OU level and I would very much like it to 🙂

    We have quite a few Service accounts and automated SQL Server installation/service account creation with the exception of SPN rights. So it would be very convenient for us if this could be set on OU level or if someone has chosen a different approach I would be very interested in hearing which.

    I guess we will have to set the rights on creation of the user object, it would have been soo convenient if it was possible to set it on the OU level 😀

    Thnx for your time!

    You can't do this at the OU level because it has to be set per account. That's why you don't see the permission there.

    K. Brian Kelley
    @kbriankelley

  • December 18, 2013 at 11:02 am

    #1674530

    K. Brian Kelley (12/18/2013)

    No. xp_cmdshell runs either in the context of the SQL Server service account or the configured proxy account (if access to xp_cmdshell is granted to a non sysadmin user and the proxy account is configured).

    It's a bit off topic but you don't ever have to grant direct execution privs to a non sysadmin user. If a stored procedure is setup correctly, they can execute the stored procedure that contains usage of xp_cmdshell without them having anything more than PUBLIC privs, which also means that they don't have the privs to run xp_CmdShell directly. Just through the proc. No certificates required, either.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 18, 2013 at 3:02 pm

    #1674635

    K. Brian Kelley (12/18/2013)

    Slevin (12/18/2013)

    rstone (12/17/2013)

    I think I was confusing delegation with impersonation. As a sysadmin, I sometimes use EXECUTE AS to impersonate a login to test permissions. In this case, once the application is using sysadmin via script, I suppose it can also impersonate a login. Would the delegation setting affect this? I have never tried to impersonate an account in the domain admin group. Does the delegation setting relate?

    Also, our service accounts are not in the domain admin group.

    I have not noticed any difference in impersonation, but I did not test that out. If I understand correctly, you would been a login of a domain admin who is not a sysadmin on your SQL server. You would then take your login who is a sysadmin and impersonate the domain admin's account. That should work, but I do not know to know extent. For instance, can you use xp_cmdshell to gain access to another server while impersonating this domain admin account? Awe...now I wish I had time to play!

    Derek

    No. xp_cmdshell runs either in the context of the SQL Server service account or the configured proxy account (if access to xp_cmdshell is granted to a non sysadmin user and the proxy account is configured).

    I was assuming xp_cmdshell was running as the service account. If it is used to connect back to SQL Server using a trusted connection (e.g., SQLCMD -E), it would be running T-SQL as sysadmin. This is where I'm wondering about impersonation - in T-SQL. I would think it could do an EXECUTE AS LOGIN at that point as it would be the same as any other connection using the service account. I'm fuzzy about what grants sysadmin impersonation rights and how far those rights extend. Perhaps it only extends to SQL Server. Or perhaps the impersonation is at the domain level and grants access to domain resources.

    I'm now also wondering what would happen if xp_cmdshell runs a C# application that impersonates while running as the service account. It's no longer a SQL Server question.

    EDIT: I took a look at one of our newer sql servers configured with virtual service accounts. I can impersoante using EXECUTE AS LOGIN. I see "SERVICE" listed in the user right "impersonate a client after authentication". It appears to be a normal for services to impersonate. I have to wonder if delegation is that risky considering that we can already impersonate.

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

  • December 21, 2013 at 3:26 pm

    #1675411

    rstone (12/18/2013)

    If it is used to connect back to SQL Server using a trusted connection (e.g., SQLCMD -E), it would be running T-SQL as sysadmin.

    Shifting gears a bit, I wonder why anyone would do such a thing. It essenctially means that you're calling xp_CmdShell to get right back where you were except you're using 3 connections (SPIDS) instead of just 1. I'm not trying to challenge or be a wise-cracker here. I'd really like to know why someone would call xp_Cmdshell to use SQLCMD to call on SQL Server which is where you started from. Is there something that can be done in SQLCmd that cannot be done in SQL Server other than writing to a file, in which case I'd likely use BCP instead?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 23, 2013 at 12:52 pm

    #1675672

    Jeff Moden (12/21/2013)

    rstone (12/18/2013)

    If it is used to connect back to SQL Server using a trusted connection (e.g., SQLCMD -E), it would be running T-SQL as sysadmin.

    Shifting gears a bit, I wonder why anyone would do such a thing. It essenctially means that you're calling xp_CmdShell to get right back where you were except you're using 3 connections (SPIDS) instead of just 1. I'm not trying to challenge or be a wise-cracker here. I'd really like to know why someone would call xp_Cmdshell to use SQLCMD to call on SQL Server which is where you started from. Is there something that can be done in SQLCmd that cannot be done in SQL Server other than writing to a file, in which case I'd likely use BCP instead?

    In general, I've learned to expect anything in vendor code. The reasons for odd stuff are likely ignorance, laziness, or convenience. In this case, I believe the application has custom modules (e.g., notification, realtime alerts) that can be executed from SQL Server procedures. Using SQL Server avoids the need to create a service to run the modules - sort of like their own version of CLR. (We won't put their DLLs on our SQL Servers, so we must not need the functionality.) The code was clever and required effort, so I think it was for convenience. It also might match what they do in other platforms.

    The best vendor approach I've seen so far was with using a trigger to do integration. The trigger called their custom exe that then sent the data as xml to another system. The exe also updated the record being processed. Yes, the exe should be blocked because the record was in the transaction of the trigger. No, it should not unblock because the trigger is waiting for the exe to finish. SQL Server does not detect this sort of deadlock. The solution was very simple - put a commit in the trigger just before the call to the exe. (I noticed the code when trying to figure out why an insert into the table never returned a record count, even with SET NOCOUNT OFF. I might have never noticed if they put a "begin transaction" after the exe call.) I ended up moving the exe out of the trigger into a job. If you only focused on the job and not it's history, you might not think I know what I'm doing. BTW, putting a commit in a trigger is not supported by Microsoft, but it did not produce a syntax error at the time.

    It was sort of natural for me to come up with a related quote. I've not used it, but I think of it from time to time. "Perhaps I'm not the only one that does not know what you are doing."

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Viewing 7 posts - 31 through 36 (of 36 total)

You must be logged in to reply to this topic. Login to reply