SQL Server – Running SQL Server Agent Jobs with least privilege accounts

Background

We have a need to create a pretty complete SQL Server BI Stack that allows developers to develop SQL Objects, create cubes ( Analysis Services ), design workflows ( Integration Services) and integrate code ( ActiveX, Batch files, and PowerShell).

 

Disclaimer

I will not advocate that SQL Server Agent should be your work engine, but if your job management needs are simple and limited, I will say it is a good “Jump Off.

Glossary

Here are some of the terms that we will use:

Credentials

  1. Credentials provide a way to allow SQL Server Authentication users to have an identity outside of SQL Server.
    • Usage Profile
      • Credentials can also be used when a SQL Server Authentication user needs access to a domain resource, such as a file location to store a backup.
    • Mapping
      • While a credential can be mapped to several SQL Server logins at the same time,  a SQL Server login can only be mapped to one credential at a time.
      • Once a credential is created, use the Login Property page to map Logins to the Credential

 

Goal

We need to import some data files into SQL Server tables.

And, to tighten our surface area, we will not use the Account that the SQL Server Engine or Agent is using.

But, to use a very specific and least favored OS or AD Account.

Outline

  1. Create Local Computer Account or Active Directory Account
  2. Create  Credential
  3. Create Proxy
  4. Grant Login access to Proxy
  5. Grant Proxy access to subsystems
  6. Create & Edit SQL Server Agent Job to use the Proxy
  7. Run SQL Server Agent Jobs a few times

 

Tasks

Create Local Computer Account or Active Directory Account

We will go with a local Computer Account and create one while we wait for our OS Admin group to provision an Active Directory Account.

Code

set _SAMAccount=DBSQLSvc
set _SAMAccountPassword=buddy6187#!

net user %_SAMAccount% %_SAMAccountPassword% /ADD

SQL – Logins/Principal

Create Login/Prncipal

Let us create SQL Principal.

Code


USE [master];  
GO  
 
declare @loginName     sysname
declare @commit        bit
 
set @loginName = 'DBLab\DBSQLSvc'
 
set @commit = 0
--set @commit = 1
 
 
begin tran

	exec sp_helplogins 
                @LoginNamePattern = @loginName

	select 
		     [src] = 'sys.server_principals - Before'
		   , tblSSP.*
		   , [suser_id] = suser_id(@loginName)	

	from   sys.server_principals tblSSP

	where  tblSSP.[name] = @loginName

	if suser_id(@loginName) is null
	begin

		print 'Create Login ' + @loginName + ' ...'

		create login [DBLab\DBSQLSvc]
		from windows;

		print 'Created Login ' + @loginName + ''

	end
	else
	begin

		print 'Login ' + @loginName + ' exists!'

	end


	select 
		     [src] = 'sys.server_principals - After'
		   , tblSSP.*
		   , [suser_id] = suser_id(@loginName)	

	from   sys.server_principals tblSSP

	where  tblSSP.[name] = @loginName
 
while (@@TRANCOUNT > 0)
begin
 
    if (@commit = 1)
    begin
 
        print 'commit'
        commit;
 
    end
    else
    begin
 
        print 'rollback '
        rollback tran;
 
    end
 
end
go

Credential

Create Credential

Let us create a credential.

Code



use [master]
go

/*
	Create a Credential
	https://msdn.microsoft.com/en-us/library/ms190703.aspx
	CREATE CREDENTIAL AlterEgo WITH IDENTITY = 'Mary5',   
		SECRET = '<EnterStrongPasswordHere>';
*/

declare @commit bit

set @commit = 0
--set @commit = 1

begin tran

	--credentialBISSQL
	if not exists
	(
		select *
		from    sys.credentials tblSC
		where   tblSC.[name] = 'credentialBISSQL'
	)
	begin

		CREATE CREDENTIAL [credentialBISSQL]
			WITH  IDENTITY = 'HRDBLab\DBSQLSvc'
			    , SECRET = 'buddy6187#!'
	end

while (@@TRANCOUNT > 0)
begin

	if (@commit = 1)
	begin

		print 'commit'
		commit;

	end
	else
	begin

		print 'rollback '
		rollback tran;

	end

end

Alter Credential

If you need to change the account you are using as your Credential or re-enter is password, please use the Alter Statement.

Code


USE [master]
GO

ALTER CREDENTIAL [credentialBISSQL] 
	   WITH 
			  IDENTITY = N'LABDOMAIN\BISDBSQLSvc'
			, SECRET = N'cookingMONSTER81712'
GO

Proxy

Let us create a proxy.

Code


use [msdb]
go

set nocount on
go

declare @proxy		sysname
declare @credential sysname
declare @commit		bit


set @proxy = 'proxyBISSQL'
set @credential = 'credentialBISSQL'

set @commit = 0
--set @commit = 1

begin tran

	if not exists
	(
		select *
		from    dbo.sysproxies tblSP
		where   tblSP.[name] = @proxy 
	)
	begin

		EXEC dbo.sp_add_proxy  
				  @proxy_name = @proxy
				, @enabled = 1
				, @description = 'Proxy Account for interfacing with other services' 
				, @credential_name = @credential 
				;
	
	end


	-- select * from master.dbo.syslogins
	-- select * from dbo.sysproxylogin
	select  
			   [proxy] = tblSP.[name]

			 --, tblSP.[user_sid]

			 , [proxyAccount]
				= suser_sname(tblSP.user_sid)

			 , tblSSS.[subsystem]

			 , tblSSS.[subsystem_dll]


	from    dbo.sysproxies tblSP

	left outer join dbo.sysproxysubsystem tblSPSS 

			on tblSP.proxy_id = tblSPSS.proxy_id

	left outer join dbo.syssubsystems tblSSS

			on tblSPSS.subsystem_id = tblSSS.subsystem_id



while (@@TRANCOUNT > 0)
begin

	if (@commit = 1)
	begin

		print 'commit'
		commit;

	end
	else
	begin

		print 'rollback '
		rollback tran;

	end

end


Grant Login access to Proxy

Let us grant Login Access to Proxy

Code



USE msdb ;  
GO  

declare @loginName     sysname
declare @proxy		   sysname
declare @commit		   bit

set @loginName = 'HRDBLab\DBSQLSvc'
set @proxy = 'proxyBISSQL'

set @commit = 0
--set @commit = 1


begin tran
  
	EXEC dbo.sp_grant_login_to_proxy  
		    @login_name = @loginName
		  , @proxy_name = @proxy
		;

while (@@TRANCOUNT > 0)
begin

	if (@commit = 1)
	begin

		print 'commit'
		commit;

	end
	else
	begin

		print 'rollback '
		rollback tran;

	end

end
go


Grant Proxy access to subsystems

Grant Proxy access to specific subsystems

Code



use [msdb]
go

set nocount on;
go

declare @proxy		   sysname

declare @id			   int
declare @idMax		   int
declare @subsystemID   int
declare @subsystemName sysname

declare @tblSubsystem  TABLE
(
	  [id]			int not null identity(1,1)
	, [subsystemID] int not null
	, [subsystem]	varchar(60)
)

declare @iProcessed int
declare @commit		bit

declare @CHAR_SEPARATOR   varchar(30)
declare @listofSubSystems varchar(4000)
declare @log		varchar(255)

set @iProcessed = 0
set @listofSubSystems = ''
set @CHAR_SEPARATOR = '; '

set @proxy = 'proxyBISSQL'

/*

	select * from dbo.syssubsystems tblSSS

*/
insert into @tblSubsystem
(
	  [subsystemID]
	, [subsystem]
)
select 2, 'ActiveScripting'
union
select 3, 'CmdExec'	
union
select 9, 'ANALYSISQUERY'	
union
select 10, 'ANALYSISCOMMAND'	
union
select 11, 'SSIS'	
union
select 12, 'PowerShell'	

set @id = 1
set @idMax = ( select max([id]) from @tblSubsystem)

set @commit = 0
--set @commit = 1


begin tran


	while (@id <= @idMax)
	begin

			select 
					  @subsystemID = tblS.subsystemID
					, @subsystemName = tblS.subsystem
			from  @tblSubsystem tblS
			where [id] = @id

		/*
			Grants the proxy proxyBISSQL access to the ActiveX Scripting subsystem.  
		*/
		if not exists
		(
			select *

			from    dbo.sysproxies tblSP

			inner  join dbo.sysproxysubsystem tblSPSS 

				on tblSP.proxy_id = tblSPSS.proxy_id

			inner join dbo.syssubsystems tblSSS

				on tblSPSS.subsystem_id = tblSSS.subsystem_id

			where   tblSP.[name] = @proxy 

			and     tblSSS.subsystem = @subsystemName

		)
		begin

			EXEC dbo.sp_grant_proxy_to_subsystem  
					  @proxy_name = @proxy
					, @subsystem_id = @subsystemID
				;  

			set @listofSubSystems = @listofSubSystems
										+ ' '
										+ @subsystemName
										+ @CHAR_SEPARATOR

			set @iProcessed = @iProcessed + 1

		end

		set @id = @id + 1

	end

	set @log = 'For Proxy Account '
					+ @proxy 
					+ ', '
					+ cast(@iProcessed as varchar(10))
					+ ' subsystems processed!'
					+ ' - List of Subsystems '
					+ ' :-'
					+ @listofSubSystems 

	print @log

while (@@TRANCOUNT > 0)
begin

	if (@commit = 1)
	begin

		print 'commit'
		commit;

	end
	else
	begin

		print 'rollback '
		rollback tran;

	end

end
go



Create & Edit SQL Server Agent Job to use the Proxy

Job Properties

jobproperties-20170113-0616pm

Job Step

Screen

jobstep-listfiles-20170113-0612pm

Explanation
  1. Type :- ActiveX Script
  2. Run as :- proxyBISSQL

Code



OPTION EXPLICIT

Function rightAlign(ValIn, WidthOut) REM As String

	Dim l
	Dim dataOut
	
	l = len(ValIn)
	
	dataOut = space(widthOut - l) + " "

    rightAlign= dataOut
	
End Function
		

Dim strFolder

Dim strLogFolder
Dim strLogFile
DIm strLogFileFullName

Dim objFolder
Dim objFile
Dim objFSO
Dim objTextFile

Dim bFolderExists
Dim bFileExists

Dim strLog
Dim objListFiles
Dim iFile

' OpenTextFile Method needs a Const value
' ForAppending = 8 ForReading = 1, ForWriting = 2
Const ForAppending = 8

strFolder = "E:\Vendor\Import\BackupFiles"
strLogFolder = "E:\Vendor\Import\Log"
strLogFile = "\backupFilesProcessing.log"
strLogFileFullName = strLogFolder & strLogFile

Set objFSO = CreateObject("Scripting.FileSystemObject")

bFolderExists = objFSO.FolderExists(strFolder)

If (bFolderExists) Then

else

	strLog = "Creating Folder " + strFolder
	
	Rem Wscript.Echo strLog

    objFSO.CreateFolder(strFolder)
	
End If

bFolderExists = objFSO.FolderExists(strLogFolder)

If (bFolderExists) Then

else

	strLog = "Creating Folder " + strLogFolder
	
	rem Wscript.Echo strLog

    objFSO.CreateFolder(strFolderLog)
	
End If

bFileExists = objFSO.FileExists(strLogFileFullName)


If (bFileExists) Then

else

	strLog = "Creating File " + strLogFileFullName
	
	rem Wscript.Echo strLog

    objFSO.CreateTextFile(strLogFileFullName)
	
End If


Set objTextFile = objFSO.OpenTextFile (strLogFileFullName, ForAppending, True)

Set objFolder = objFSO.GetFolder(strFolder)

Set objListFiles = objFolder.Files

iFile = 0

strLog = "@ " & CSTR(Now) 
objTextFile.WriteLine(strLog)

strLog = "====================="
objTextFile.WriteLine(strLog)

For Each objFile in objListFiles

	iFile = iFile + 1

	strLog = "File " + rightAlign(iFile, 4) + CSTR(iFile) + ") " + objFile.Name
	
	objTextFile.WriteLine(strLog)

Next

objTextFile.Close

Set objFSO = Nothing
set objTextFile = Nothing

To Do

Will come back and identify some of the errors we encountered.

 

References

 

  1. Transact-SQL Reference (Database Engine) > System Stored Procedures (Transact-SQL) > SQL Server Agent Stored Procedures (Transact-SQL)
    • sp_grant_login_to_proxy (Transact-SQL)
      • sp_grant_login_to_proxy
        Link
    • msdb.dbo.sp_delete_proxy ( Transact-SQL )
      • sp_delete_proxy
        Link
  2. Transact-SQL Reference (Database Engine) > System Tables (Transact-SQL)  > SQL Server Agent Tables (Transact-SQL)
    • dbo.sysproxies
      • dbo.sysproxies
        Link
    • dbo.sysproxylogin (Transact-SQL)
      • dbo.sysproxylogin
        Link
    • dbo.sysproxysubsystem  (Transact-SQL)
      • dbo.sysproxysubsystem
        Link
    • dbo.syssubsystems (Transact-SQL)
      • dbo.syssubsystems
        Link
  3. VBScript Example
    • ScriptingGuy1
      • How Can I Get a List of All the Files in a Folder and Its Subfolders?
        Link
    • VB Script to File
      • VBScript Write File
        Link
  4. System Passwords
    • Secured Passwords
      • Symantec Password Generator
        Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s