Scripting an application role with a hashed password

Question

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'd really like to be able to have the hashed password in the script, something like:

CREATE APPLICATION ROLE [MyAppRole]
WITH PASSWORD = 0x12345678 HASHED
    , DEFAULT_SCHEMA = [dbo];

Is this possible? Presumably the hashed version of the application role password is stored somewhere in the database.

Using the SQL Server Management Studio "script to ->" functionality, the Application Role is scripted with a new password, as:

/****** Object:  ApplicationRole [MyAppRole]    Script Date: 9/22/2015 10:18:12 AM ******/
/* To avoid disclosure of passwords, the password is generated in script. */
declare @idx as int
declare @randomPwd as nvarchar(64)
declare @rnd as float
select @idx = 0
select @randomPwd = N''
select @rnd = rand((@@CPU_BUSY % 100) + ((@@IDLE % 100) * 100) + 
       (DATEPART(ss, GETDATE()) * 10000) + ((cast(DATEPART(ms, GETDATE()) as int) % 100) * 1000000))
while @idx < 64
begin
   select @randomPwd = @randomPwd + char((cast((@rnd * 83) as int) + 43))
   select @idx = @idx + 1
select @rnd = rand()
end
declare @statement nvarchar(4000)
select @statement = N'CREATE APPLICATION ROLE [MyAppRole] WITH DEFAULT_SCHEMA = [dbo], ' + N'PASSWORD = N' + QUOTENAME(@randomPwd,'''')
EXEC dbo.sp_executesql @statement
GO

Clearly, that is not helpful in my case, although it does provide an interesting method of generating random passwords.

---

Based on the answers so far, I have created a Connect suggestion to add support for this to the product:

• CREATE APPLICATION ROLE should support the 'HASHED' syntax for passwords.

Answer 1

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:

1. 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,
2. Using regular roles instead of application roles.

Answer 2

There is nothing in the syntax that makes this possible

CREATE APPLICATION ROLE (Transact-SQL) - Books Online

You can get around this with creating the approle in a template database and have the script restore that. One implementation (Dynamics NAV) creates an approle in code and stores the encrypted application role password in a table in the database making the client decrypt the role password.