THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

SQL Server 2008 R2 DACPAC Bug - Cannot insert the value NULL into column 'created_by', table 'msdb.dbo.sysdac_instances_internal'

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:

image

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

image

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:

image 

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:

Published Tuesday, July 27, 2010 12:14 AM by Jonathan Kehayias

Comments

No Comments
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement