April 2, 2019 at 10:51 pm
I am working at a smaller shop. Here, I get to perform typical DBA duties, but then I also serve as a T-SQL and SSIS Developer. On top of that, some of our applications use AD authentication within SQL Server. We still also have some apps that authenticate with a SQL Server login, which matches the syntax of our AD account login, minus the domain name.
Prior to my arrival, people in the same role as myself used their singular AD account to fulfill all needs mentioned above. It causes some confusion, because the DBAs are implicitly granted access to applications with AD authentication, and SSIS development is always causing people issues when file system access is needed, and a personal account has access to a LAN drive while the SQL Agent service account doesn't, or vice-versa.
I'm thinking there's got to be a better way, but I don't want to drown myself and fellow backup DBA with a ton of logins. I'm considering floating the idea of at least a 3rd login, and have something like the following. Please comment, or suggest what you've found to be a good approach, so that we can craft the best long-term solution for this. Here's my current idea:
- Login #1: AD login for DBA role and SSIS development, placed into same AD groups for LAN access as any AD login used as a SQL Agent Service Account
- Login #2: AD login for Application use (aka my normal account)
- Login #3: SQL Server login for Application use
I've tinkered around with SSMS, and you can right-click when launching it, and log in as a separate user. Same goes with VS, for SSIS development. In any case, does this seem untenable, or is there a better way?
Thanks,
--=Chuck
April 3, 2019 at 4:19 am
Well... a ton of logins might be the way to go. You need to isolate the security so that each thing, define thing, can only do the minimum needed to accomplish the job. This helps to ensure that you don't get hacked. Minimum, two logins for yourself, one that gets you production access and one that puts you on the network in general. This should be separate from any and all service accounts that run whatever service we're talking about. Each of those services should also be isolated. It makes for a lot more to manage, yes, however, it's also more secure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 3, 2019 at 10:26 am
In addition to Grant Fritchey's post: create credentials and use proxy accounts (see: https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/) to run SSIS packages from SQL Agent jobs.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply