Scripting an application role with a hashed password

I need to script an application role with a hashed password, so I can copy it from one database to another.

Consider the following example code, which uses an Application Role to provide elevated access to a non-trusted user:

USE tempdb;

CREATE LOGIN LimitedLogin
WITH PASSWORD = 'Password1'
    , CHECK_POLICY = OFF
    , CHECK_EXPIRATION = OFF;

CREATE USER LimitedLogin
FOR LOGIN LimitedLogin
WITH DEFAULT_SCHEMA = dbo;

CREATE APPLICATION ROLE MyAppRole
WITH PASSWORD = 'Password2'
    , DEFAULT_SCHEMA = dbo;

EXEC sp_addrolemember @rolename = 'db_datareader'
    , @membername = 'MyAppRole';

CREATE TABLE dbo.Numbers
(
    [Number] int CONSTRAINT PK_Numbers 
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1) NOT NULL
);

INSERT INTO dbo.Numbers
VALUES (1)
    , (2);

GO

Once we've created the test setup in tempdb, we can login as the [LimitedLogin] user, and run the following:

-- login as [LimitedLogin]

USE tempdb;

SELECT *
FROM dbo.Numbers;

The following error is returned, as expected:

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Numbers'
    , database 'Test', schema 'dbo'.

However, once we execute the sp_setapprole with the appropriate password, we can see the desired results from the dbo.Numbers table:

DECLARE @cookie VARBINARY(8000);
EXEC sp_setapprole @rolename = 'MyAppRole'
    , @password = 'Password2'
    , @fCreateCookie = 1
    , @cookie = @cookie OUT;
SELECT @cookie;

SELECT TOP(10) *
FROM dbo.Numbers;

EXEC sp_unsetapprole @cookie = @cookie;

I'd like to be able to automate creating the Application Role by scripting it from a source database to be applied to a destination database. I can easily perform the majority of that by:

SELECT 'CREATE APPLICATION ROLE ' + QUOTENAME(dp.name) + '
WITH PASSWORD = ''xxxx''
    , DEFAULT_SCHEMA = ' + QUOTENAME(dp.default_schema_name) + ';'
FROM sys.database_principals dp
WHERE dp.type_desc = 'APPLICATION_ROLE';

However, I

You can connect using the DAC (Dedicated Administrator Connection), and pull the password column from sys.sysowners. First, connect using:

ADMIN:Server\Instance

Then:

SELECT password_hash = [password]
  FROM sys.sysowners
  WHERE name = N'MyAppRole';

This view is only visible when using the DAC, and the column is not exposed in the parent view that is visible (sys.database_principals). Please be careful with the DAC, of course.

That all said, this doesn't help you. CREATE APPLICATION ROLE is much different from CREATE LOGIN, in that you can't supply a hashed password, only plain text. And don't even think about reverse engineering the hashed value, because modern versions of SQL Server use elaborate methods to encrypt a password. In fact if you try this yourself you will see that a different hash is created every time, even in the same statement:

SELECT PWDENCRYPT(N'foo'), PWDENCRYPT(N'foo');

Results (on my machine, this time), noting that yours will vary:

0x0200185968C35F22AF70...   0x0200D6C77A1D84A8467F...

So, I would recommend either:

• Storing the application password in source control somewhere, or wherever you store other system passwords currently, and using that to generate the script to deploy the app roles to another server (or source control the entire CREATE APPLICATION ROLE script); or,

• Using regular roles instead of application roles.

StackExchange Database Administrators Q#115778, answer score: 6