Is it possible to convert existing users from SQL Server authentication to Windows (AD) authentication? (SQL Server 2008 R2)

Question

We have just undergone a bare-metal restore of an SBS 2003 server running our SQL database and are in the final stages of configuration before going live again.

Previously, we had 20 users accessing this database via SQL authentication. This wasn't ideal, as users had multiple sets of credentials to remember-but the person who set it up decided to do it this way. (Any ideas why someone would choose this over AD authentication?)

I am wondering if there is any way to move from using SQL user accounts to AD credentials? If so, what would be the best practice method for achieving this with an existing database?

Any advice would be much appreciated!

Answer 1

The security concept of SQL Servers differentiate between login and user. A login is at the SQL Server instance level and can be mapped to different users for different databases, as long as the database user is a SQL user and not a Windows user.

You could create the Windows login and the user (both having the same name) followed by script that copies the permissions from the SQL user to the Windows user. (Search for "sql server clone user permissions").

Answer 2

SQL Users are still available primarily to give access from systems that don't have easy AD access. For example in my company our UNIX systems do not use AD so applications written on those systems need to use a SQL Login in order to connect to a SQL Server instance. I'm told it is possible to set a UNIX system up so that it can use AD but ours just aren't set up that way.

As far as copying the SQL Server logins/users from SQL Server auth to AD auth, I'm afraid it just isn't possible to do directly. In fact it isn't even possible to transfer a User from a SQL Server login to an AD login. You get the following error:

Msg 33017, Level 16, State 1, Line 1
Cannot remap a user of one type to a login of a different type. For example, a SQL user must be mapped to a SQL login; it cannot be remapped to a Windows login.

There are however a number of tools out there that will script out permissions and you can then adjust the script to use AD authentication.

For example at a database level you can use the built in tasks/generate scripts.

From there you can specify that you want to script user only permissions. You will end up with a script that looks like this.

USE [UserTest]
GO
/****** Object:  User [UserName]    Script Date: 10/19/2014 8:42:08 AM ******/
CREATE USER [UserName] FOR LOGIN [LoginName] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [UserName]
GO

From there just do a replace of [UserName] WITH [Domain\ADLogin]. You will have to create all of the logins manually but that's pretty easy too.

CREATE LOGIN [Domain\ADLogin] FROM WINDOWS

I do realize that could be a bit of a pain if you have hundreds of databases but if that's the case then you can use various on-line tools\scripts to find & generate your scripts for you.