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:
Cannot insert the value NULL into column 'created_by', table 'msdb.dbo.sysdac_instances_internal'; column does not allow nulls. INSERT fails.
I decided to waste a bit of time troubleshooting this, just as if I had gotten this error message from an internally developed database by first looking at the DDL statement for the table.
CREATE TABLE [dbo].[sysdac_instances_internal](
[instance_id] [uniqueidentifier] NOT NULL,
[instance_name] [sysname] NOT NULL,
[type_name] [sysname] NOT NULL,
[type_version] [nvarchar](64) NOT NULL,
[description] [nvarchar](4000) NULL,
[type_stream] [varbinary](max) NOT NULL,
[date_created] [datetime] NOT NULL,
[created_by] [sysname] NOT NULL,
CONSTRAINT [PK_sysdac_instances_internal] PRIMARY KEY CLUSTERED
(
[instance_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UQ_sysdac_instances_internal] UNIQUE NONCLUSTERED
(
[instance_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[sysdac_instances_internal] ADD DEFAULT ('') FOR [description]
GO
ALTER TABLE [dbo].[sysdac_instances_internal] ADD DEFAULT (getdate()) FOR [date_created]
GO
ALTER TABLE [dbo].[sysdac_instances_internal] ADD DEFAULT ([dbo].[fn_sysdac_get_currentusername]()) FOR [created_by]
GO
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:
CREATE FUNCTION [dbo].[fn_sysdac_get_currentusername]()
RETURNS sysname
BEGIN
RETURN dbo.fn_sysdac_get_username(SUSER_SID());
END
GO
So we have a function that calls another function passing the SUSER_SID() in as the parameter. This wasn’t very helpful, so I used sp_help_text again to dump out the definition of this other function:
CREATE FUNCTION [dbo].[fn_sysdac_get_username](@user_sid varbinary(85))
RETURNS sysname
WITH EXECUTE AS OWNER
BEGIN
DECLARE @engineEdition int = CAST(SERVERPROPERTY('EngineEdition') AS int);
DECLARE @current_user_name sysname;
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:
https://connect.microsoft.com/SQLServer/feedback/details/577174/dacpac-fn-sysdac-get-username-returns-null-value-for-not-null-created-by-column-on-sysdac-instances-internal-table?wa=wsignin1.0#
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: