While trying out the new DACPAC feature in SQL Server 2008 R2 recently, I ran into a bug that makes no sense to me at all. In my environment we use an Active Directory Group for the SQL Database Administrators and that group is a member of the sysadmins fixed server role inside of SQL Server, which I’d suspect is pretty standard in most environments. The new Data-tier Application feature in SQL Server 2008 R2, has a bug that makes it impossible to Register a new Data-tier Application if the account being used to create it is not explicitly created as a login inside of SQL Server.

Registering a Data-tier Application is a straight forward process. Right click on the database and select Tasks –> Register as Data-tier Application. Click Next on the first screen which looks like the below screenshot:

Once discovery completes, you’ll get a screen like the following:

Clicking Next again will cause SQL to register the Data-tier Application, or at least attempt it. However, if you use an AD Group to control rights and your account only has access to SQL Server through that group, the registration will fail with the following underlying error message:

The bold line above was the interesting part of the DDL Script since it was a default constraint on the column that was specified in the error. So I used sp_help_text to dump the definition of that function out to see what it was doing:

IF (@engineEdition = 5) BEGIN --SQL Azure does not have syslogins. All the logins reside in sql_logins SELECT @current_user_name = name FROM sys.sql_logins where sid = @user_sid END ELSE BEGIN --OnPremise engine has both sql and windows logins in syslogins SELECT @current_user_name = name FROM sys.syslogins where sid = @user_sid END

RETURN @current_user_name; END

This is where I really didn’t understand the implementation of this. I can understand the creation of a helper function that checks to see if this is SQL Azure to a point, since Azure has limitations, and not all of the coded functionality in standard SQL Exists in Azure, but why Microsoft chose to query sys.syslogins here is beyond me. This explains why the registration process fails with a NULL insert for accounts belonging to an AD Group since there isn’t an entry in sys.syslogins for the account, there is only an entry for the AD Group. What makes this really confusing is they chose to use SUSER_SID(), and then didn’t use the SUSER_NAME() function, which returns the appropriate login name for AD Group members.

I filed this as a bug on Connect, so if you happen to try and use the DACPAC feature and run into this, vote for it:

If a Active Directory Group is used to control permissions in the database engine the fn_sysdac_get_username(SUSER_SID()) function call to enforce the DEFAULT CONSTRAINT on the msdb.dbo.sysdac_instances_internal returns NULL because a group member in AD doesn't have an entry in sys.syslogins which is what is being used to lookup the login name for the SID. This prevents registration of Data Tier applications unless the AD Account is explicitly added to the server, defeating the purpose of using AD Groups. The error returned is: