DTSX Pkg From Stored Procedure (completely frustrated)

Question

DTSX Pkg From Stored Procedure (completely frustrated)

Viewing 4 posts - 16 through 18 (of 18 total)

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

Garth Zaleschuk SSC Veteran Points: 288 More actions · Answer 1

I ran this by our Domain guys and they are not sure of the workability of this scenario.
I figure the user name is 'NT SERVICE\SQLSERVERAGENT'. It is a windows user and the default I schema is 'dbo' Owned and membership, nothing is checked. Securables and Extended are empty.

So am I to have a new domain user set up as 'Our_Domain\SQLSERVERAGENT'? Windows user and not SQL user, correct? Privileges to match my own (and if this is the case why would I not use the user name we use now on the box as it has unfettered access.)

sgmunson SSC Guru Points: 110640 More actions · Answer 2

Garth Zaleschuk - Wednesday, February 28, 2018 12:09 PM
I ran this by our Domain guys and they are not sure of the workability of this scenario.
I figure the user name is 'NT SERVICE\SQLSERVERAGENT'. It is a windows user and the default I schema is 'dbo' Owned and membership, nothing is checked. Securables and Extended are empty.
So am I to have a new domain user set up as 'Our_Domain\SQLSERVERAGENT'? Windows user and not SQL user, correct? Privileges to match my own (and if this is the case why would I not use the user name we use now on the box as it has unfettered access.)

I don't know enough about your environment to make specific recommendations to be applied here, but in general, you don't hand that Agent Service Account unfettered access. You grant it what it needs on a case by case basis, recognizing that protecting your environment from a bad actor (regardless of whether that's a hacker from outside or a saboteur from within), requires constant effort and maintenance, and that such things have a cost. Good security is NOT inexpensive. The difference between what you were doing with a proxy is that your inherent set up for SQL Server deviates significantly from best practices, and particularly in the security of your system. Service accounts should always be a domain account and should always be granted only enough permissions to do what needs doing. This has been a SQL Server best practice for quite some time now. When you operate all your SQL Services with domain accounts, you lower your attack surface significantly as long as those accounts only have access to what they need to have access to. Generally, that means using global groups or domain local groups, to which those accounts are added, and then those groups become part of database roles and/or NTFS permissions. If you don't have a corporate DBA to do those things, it may be long past time to hire one - and a high-quality one with as much experience as you can get your hands on.,

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Garth Zaleschuk SSC Veteran Points: 288 More actions · Answer 3

Thanks for the confirmation of Best Practices...I am in the choir and at times lead the way, however nasty budgetary considerations have always hamstrung me and so I just solider on the best I can... If I can't get the agent to run a pkg I will revert to MS Access to trigger the stored proc...

Garth Zaleschuk SSC Veteran Points: 288 More actions · Answer 4

OK got it... Create the DTSX pkg and save as a local file, create a Stored proc with the line

exec xp_cmdshell 'dtexec.exe /F "P:\RPT-GL2-001.dtsx"'

Then schedule in the Agent as a 'Transact-SQL script (T-SQL)

Thanks to all who responded and although I was not given the answer directly u guys pointed me in directions which helped me resolve the issue.