Service account or login RRS feed

  • Question

  • I work in an environment where we are working in a DEV_DOMAIN and a PROD_DOMAIN using Windows authentication. The developers are added to a Windows Group, say DEV_GROUP, and we are added to a a group in the PROD_DOMAIN, say DEV_READER (to allow us to look, but not change anything, objects or data.). On PROD_DOMAIN, we also have a Windows OPS_GROUP, which can run things and view data, but they cannot change any objects or data. These windows groups are mapped to SQL trusted logins, and have the groups have their permissions allocated by the DBA. Of course, the Active Directory people manage who is added or deleted from the Windows Groups.

    My question is this: When a user from the OPS_GROUP logs onto a Production Server and launches the jobs, he is attached to that job and is the logged in user for the whole time. If he wants to log out and go home, it stops the PROD jobs. So, it turns out that he has to stay logged in for days at a time. How do you spin off that job to, let's say, a 'service account' or some other account that will actually run the job? And, if so, do we lose the logging of the original user that kicked it off? Right now, we use the task scheduler to launch a Powershell Script, which in turn, kicks off a DTSX package and/or a Stored Procedure with the credentials of the logged in group user after he logged onto his workstation. I want to spin that off so it will run (under this service account) after the original user logs off. And if I do this, do I have to give him the username and password to the service account (I assume I cannot use Windows Authentication for this login, or can I somehow?)?

    I am sure this is done every day all across the land, but I have been struggling with how to do this for quite some time. Any hints, even if not the whole solution would be appreciated here.

    • Edited by duanewilson Wednesday, January 10, 2018 9:25 PM
    Wednesday, January 10, 2018 9:21 PM

All replies

  • Typically this is done using SQL Agent.  Any member of the SQLAgentUserRole in msdb can create a TSQL job step, and schedule or execute the job.  The SQL Agent service account will log into SQL Server, and then impersonate the job owner before running the TSQL job step.

    You can also use SQL Agent proxies for other kinds of job steps, but this requires storing a user name/password in a SQL Server Credential object.


    Microsoft Technology Center - Dallas
    My blog

    Wednesday, January 10, 2018 9:53 PM
  • This is great information. I never thought of it before. Part of the reason I didn't think of it, though, because my company won't allow us to use the SQL Agent.

    Is there any other way of doing that? (I will definitely remember this when and if they change their mind).

    Duane Wilson

    Wednesday, January 10, 2018 10:30 PM
  • Wow! Agent is built for this stuff. "I want to carry people from this city to the next city, but our company policy don't allow us to use any type of vehicle." Terrible analogy, but you get the point. Talk to the company and have them sort out their thinking!

    As an aside: Creadentials and Agent Proxies (having a job being execute by "somebody else") is only available for the non-TSQL jobsteps.

    For TSQL jobsteps, SQL Server will execute as "you" (the job owner). Only way around that is of you have permissions to use EXECUTE AS, which probably mean that you are either a database owner or possibly even a sysadmin (depending on whether you want to impersonate a user or login). But is sounds that the job type likely will be SSIS or CmdExec, for which Agent Proxies/Credentials are available.

    Agent job and Windows scheduled tasks are the methods I can think of.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, January 12, 2018 10:01 AM
  • Thank you. I wonder how the big shops do all this.

    We have SSIS jobs (File system type) that are invoked through Powershell (used to be batch files). The Powershell jobs use XML config files to set environments, settings, what server, etc. These SSIS packages are passed parameters that ultimately come from the XML config files and ETL the source files (mostly csv files) into the database, and then other Powershell scripts run stored procs in database, and thirdly, other Powershell jobs run extract type SSIS packages to export data into a folder and some do some ftp or sftp work.

    We have operators and developers, but as developers, we would like to test these scripts as operators rather than with full developer privileges. I would have thought this to be quite common, but it seems very difficult in the end, especially since we log into a domain and are assigned certain rights through AD, and of course, that maps to varying rights on the SQL Server. (I do understand it would be improper to allow developers the reverse on PRD machines and scripts, but I thought there may be a way to assume a different identity on windows in order to test the jobs as PRD operators on our lower environments.

    Duane Wilson

    Wednesday, January 17, 2018 5:20 PM