This article shows how to force SQL and Web Developers to not use SA account while other services are already deployed to connect to SQL Server via SA account. So we will not touch existing services and applications only developers must be prevented.
Feel yourself a lucky guy when you are a DBA and you never met an environment where all the users, developers, services, applications and everything access the SQL Server via the SA (system administrator) account.
Unfortunately, usage of SA limits DBA's (and DevOps) toolset for managed releases, secure database and server auditing and avoiding human errors. If you are connected to SQL Server with SA account then you explicitly have permission for every possible command, operation and functionality. You cannot limit the permission set of SA. The only possibility is to disable SA globally on the whole server.
But while SA is a commonly used login across the company than you have a good chance that all services, web applications connect to your SQL Service via SA. So disabling the SA account globally is not a real solution.
Anyways it is not a goal to deny access for developers to the Live database server. We should accept that sometimes developers must have access to (at least read) the Live or Production database. This article is not about to deny developers from SQL Server. It is just about controlling the access and give only the minimal required permissions.
Let's define goals first:
User/Developers must not connect to SQL Server by SA account only their own Windows Domain user account (Windows Authentication).
Applications, services, websites, ETL or reporting processes can still connect to SQL Server by SA if they are running on a dedicated server and not on a user's desktop.
If a user has a remote desktop access on SQL Server then that user can connect to SQL Server locally by SA. This is a spare solution in case of any windows authentication problems.
We need the following components:
1. Server white list: these server machines host applications, services, ETL processes etc. This list contains the domain name of the server and we place it into a table in msdb database in SQL Server.
2. Windows domain group: for developers and users who need to connect to SQL Server. Of course, SQL Server operation system must be entered into that AD (a domain administrator can do it). In this example, we use
Domain Users which is a default group in all AD domains. This contains all users in a domain.
3. A login for that AD group: this (Windows Authenticated) login will ensure the access for users arriving from anywhere. We need to run the following code. Replace DOMAIN with your current domain and Domain Users to your custom group:
CREATE LOGIN [DOMAIN\DomainUsers] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
If developers used to have access to everything (all databases and objects) then grant them (except master and distribution):
EXEC sp_msforeachdb '
IF ''?'' NOT IN (''master'', ''distribution'')
CREATEUSER [DOMAIN\DomainUsers] FOR LOGIN [DOMAIN\DomainUsers];
ALTERROLE [db_owner] ADDMEMBER [DOMAIN\DomainUsers];
4. Login trigger on the server: this is the most important component. This prevents logins with sa from users desktop:
ON ALL SERVERFORLOGONASSET NOCOUNT ON;
SYSTEM_USER = 'sa'
(EXISTS(SELECT * FROM msdb.dbo.HostWhiteList WHERE HostName = HOST_NAME()) OR HOST_NAME() = SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
SYSTEM_USER <> 'sa'
ENABLE TRIGGER [TR_ServerLogin] ON ALL SERVER
WARNING! Don't change this trigger code otherwise you can ban yourself from the server even if you are an administrator!
That's all. After that, we should maintain the
Whitelist table in msdb.
If any of users try to connect to SQL Server by SA then they will get the following message:
5. Disable remote desktop access for users: this option remains for administrators.
WARNING! Don't change the trigger code. You can ban yourself from the server using SA.
If this happens then you can use
DAC (Dedicated Admin Connection). Login to the server via DAC disables login triggers.