Managing SQL Logins in different environments

In this post I want to show you how we can manage SQL Server logins to avoid a very common problem Speaking of security, in SQL Server we have the possibility of authenticating in two different ways, one it is based on Windows Authentication and allow Windows users (from a local computer or Windows domain) to connect to the server using their credentials and the other is based on SQL logins that are handled within the SQL Server instance.

Windows authentication is always enabled and cannot be disabled (in SQL Server running on Windows) and on the contrary, SQL Server authentication needs to be enabled either during installation by providing a password for [sa] or at any time afterwards by going to

Server properties -> Security

 
Background

Most servers out there would have both enabled so sooner or later us, DBA’s, need to deal with SQL logins, but there is more than providing a name and a password (a strong one, of course).

If you also have different environments, most likely you want to create different logins to avoid DEV apps or users connecting to LIVE or vice-versa.

But when you have different logins and by default database users, when you need to refresh your DEV (TEST, QA…) you’d need to apply all the permissions granted again to the right user because the login does not exist in that environment. Does it sound familiar?

In this post I will show you how you can handle this problem in a very simple way.

 
Creating a SQL Login

We can create a login in SQL Server Management Studio by navigating to

Server -> Security -> Logins -> New login

As usual we need to provide as much information as possible, including the default database and mapped database users, do not forget to deselect db_owner role for the new user. To be honest, I don’t get why it is checked by default.

 
Regardless we click ‘Ok’ or ‘Script’ there is something that is missing and it’s probably the most important bit. Even once it is created and we script it out, it won’t show.

Do you know already what I’m taking about? Right.. the SID.

 
It’s all about the SID

As opposed as people may think, SQL Server uniquely identifies logins based on their SID’s, not their names.

SID’s (Security Identifiers) can be provided by a Windows domain or computer or by SQL Server. That is key for what we’re trying to achieve. There is no name matching mechanism so all is based on the SID of the principal that is trying to connect to our SQL Server.

SID are also the mechanism to match server principals (logins) to database principals (users).

AS I said, there is no way in SSMS to script a SQL Server login including the SID, but some time ago I published my stored procedure [dbo].[sqlg_securityAuditServerLogins] which does the job for me (and for you too)

 
How avoid problems

The best way of fixing a problem is not having a problem in the first instance, so let’s see how we can approach having different logins, one per environment that will allow access to the database regardless which of them we are.

We’ll need:

• One login name per environment, each with different password
• One database user and only one for all environments

 
The first login can be done either way, using TSQL or the UI, it doesn’t matter.

USE [master]
GO
CREATE LOGIN [SalesApp_dev] WITH PASSWORD=N'StrongPassword.123!'
	, DEFAULT_DATABASE=[AdventureWorks2014]
	, CHECK_EXPIRATION=OFF
	, CHECK_POLICY=ON
GO
USE [AdventureWorks2014]
GO
CREATE USER [SalesApp] FOR LOGIN [SalesApp_dev]
GO
GRANT SELECT ON [Person].[Address] TO [SalesApp]

Note that the database user does not include _dev, because I personally hate finding _dev stuff in my production servers, but it’s totally up to you.

Once we have created our login, we can script it out, including the SID, with my stored procedure. Just scroll to the last column to find it.

EXECUTE master.dbo.sqlg_securityAuditServerLogins @loginName = N'SalesApp_dev'

The script generated is as follows

USE [master]
GO
CREATE LOGIN [SalesApp_dev] WITH PASSWORD = 0x02001144A8F0BFE5A5C2C5E62656B13289912E02A1A483F77BD58D172F8779FBD495504D9F1196545B87983921D0629AB1DD4D0788DC1C1C813DBA66639F6FE0813010DC38DB HASHED
, SID = 0xE27321DFC1DDFC4B83D0398823792E58
, DEFAULT_DATABASE = [AdventureWorks2014]
, CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
GO
GRANT CONNECT SQL TO [SalesApp_dev]
GO

Note the password is hashed and it brings the SID. So what we need to change before running this in other environments is:

• login name
• password

 
and that would be it. We can take that now and create the login in our production server (if we consider this was our dev)

Let’s go create the login in our “production” server which is my other instance MSSQL2016.

USE [master]
GO
CREATE LOGIN [SalesApp] WITH PASSWORD = N'StrongPassword.456!'
, SID = 0xE27321DFC1DDFC4B83D0398823792E58
, DEFAULT_DATABASE = [AdventureWorks2014]
, CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
GO
GRANT CONNECT SQL TO [SalesApp]
GO

Now we are ready to see the trick we have been preparing.

Usually it would be the other way round, but in this case we are going to take a backup of the ‘Dev’ server and restore it into ‘Prod’ and then you can see how everything works as a treat.

-- Run this in SQLDOUBLEG\MSSQL2014 (aka dev)

BACKUP DATABASE [AdventureWorks2014] TO DISK ='AdventureWorks2014_SalesApp.bkp'

-- Copy the file to a location that can be accessed by the other instance

-- Run this in SQLDOUBLEG\MSSQL2016 (aka prod)


RESTORE DATABASE AdventureWorks2014
FROM DISK = 'AdventureWorks2014_SalesApp.bkp' 
WITH RECOVERY, REPLACE
, MOVE 'AdventureWorks2014_Data'	TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQL2016\MSSQL\DATA\AdventureWorks2014_Data.mdf'
, MOVE 'AdventureWorks2014_Log'		TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQL2016\MSSQL\DATA\AdventureWorks2014_Log.ldf'

GO

 
If we now check the mappings of the database users from the different servers, we can see that they are mapped to the right logins

-- Run in SQLCMD mode
:CONNECT SQLDOUBLEG\MSSQL2014

USE AdventureWorks2014

SELECT @@SERVERNAME AS server_name
		, p.name AS dbuser_name
		, p.sid AS dbuser_sid
		, SUSER_SNAME(p.sid) AS login_name
	FROM sys.database_principals AS p
	WHERE name = 'SalesApp'

GO

:CONNECT SQLDOUBLEG\MSSQL2016

USE AdventureWorks2014

SELECT @@SERVERNAME AS server_name
		, p.name AS dbuser_name
		, p.sid AS dbuser_sid
		, SUSER_SNAME(p.sid) AS login_name
	FROM sys.database_principals AS p
	WHERE name = 'SalesApp'

And that is my dear readers how you can overcome this very common problem without having to remap the logins and users every time you restore a database from one environment to another.

 
Conclusion

Although is recommended to use Windows authentication every time if possible, there are legit reasons to use SQL Logins instead, that’s why they exist, but with a bit of knowledge of the internals, we can make their maintenance a little bit easier.

Thanks for reading!