May 16, 2016 at 3:35 pm
(This is a general question about SQL Server administration. Wasn't really sure where to put this so please forgive me if the question could have really better been put elsewhere.)
First, a little bit of background on my personal and professional growth vis-à-vis SQL Server authentication. When I first started working with SQL Server, probably in 1997 or thereabouts, I was on a small team of developers. None of us could truly be called a DBA. We didn't have the budget for one and so we did our own management of the SQL 6.5 (I believe it was) we had at the time. My boss, at that time, created a password for the sa account and that's what we used in our code, reports, ODBC connections, etc. Deplorable, but hey, we didn't know any better.
Some years passed. I learned of this website at one point way back when and joined it. (I think the account/profile I created on SSC went away when I changed email addresses; I don't remember. Hey, it was a long time ago.) I learned back then that it was a bad idea to put sa into applications, especially because of the .Config files which were always plain text. If anyone got access to our website, then all they'd have to do was open the Web.Config file and they're in, baby! Sometime around then my first boss at my previous job left. After he I spent months weaning us off of relying upon sa for everything. What I did was created a regular account, without any privileges, in Active Directory (AD). That account couldn't log in interactively. Then I made that account have access to the databases through our applications, websites, reports, etc. The connection string was still in .Config files, but I thought it was at least better, as no one could log into our system using that account and create havoc. (No, I didn't think of a hacker creating some sort of batch file and running a scripting job using that account. Sorry, that was my bad.) Anyway, up until I left my previous job, that is how everything worked. This special account at most had CRUD privileges to any table, view or execute privileges to stored procedures. I honestly thought that was the best approach.
But of course I began to read here on SSC, that really the best practice is anyone needing access to SQL Server tables, views, SP's, etc. would be through their Windows identity. Or at least indirectly. What you then do is create groups in AD, put those AD groups into SQL Roles and then assigning appropriate access to SQL objects that way. And basically that's how they do it here where I now work.
However, I still see problems. For example, everyone has MS Office on their desktops and/or laptops. Many people know how to use MS Access here. In some quarters people just pop open Access, go directly against the tables and just have at it. Sometimes it reminds me of the proverbial "wild west". Upper management has published a edict saying, in essence, "thou shalt not use Access, yeah verily". But so many people use so many applications that are accessible only through MS Access, that they can't take Access away from them. So its business as usual.
So please tell me, how is it that tying database access to people's Windows accounts the best solution, given our current scenario?
Kindest Regards, Rod Connect with me on LinkedIn.
May 16, 2016 at 4:31 pm
Here is the concept behind AD authentication: Users should belong to AD groups depending on what broad level of access they need to database servers (ie: MyCorp\Developers, MyCorp\QA, MyCorp\DBA). Within SQL Server, ideally you should create logins for AD groups (MyCorp\QA), NOT individual logins (ie: MyCorp\JSmith). This called "role based security".
Applications should run under the context of service accounts (ie: MyCorp\PointOfSale), and nobody but the AD admin should know or be able to change these account passwords. These application service accounts (just like AD users) connect to the database using SSPI (windows authentication), so if someone stumbles upon a web.config file within your source code versioning system, they only see the account name, not the password.
Each group is added as a user only within the database(s) for which they need access, and within each database each user group should be granted only the minimal permissions required for their proper business functions. For example, MyCorp\Sales has select (but not insert or update) permission on Sales related tables. Other groups may only need execute permission on specific stored procedures without direct SELECT/INSERT/UPDATE permission on any object. This is called the "principle of least privilege". Nobody and nothing should be a member of SYSADMIN except for members of the MyCorp\DBA group. If a user logsin to the database via any application (whether it be SSMS, MS Access, or PowerBI) they don't have carte blanche permission to do whatever they want, only what you have granted members of their AD group.
So, to extend or deny a specific user database access across the enterprise, you simply add or remove them from AD groups. No intervention is needed on behalf of the DBA.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply