mssqltips logo

Script out SQL Server Credentials and Proxies

By:   |   Updated: 2018-11-21   |   Comments (1)   |   Related: More > Security

Problem

It is often a requirement to migrate a database from one SQL Server instance to another and that often involves migrating server objects used in the SQL Server Agent Jobs, but there is not an easy option to export credentials and proxies that are used for running SQL Server Agent jobs. In this tip we look at a way we can automtically generate scripts to recreate the credentials and proxies.

Solution

We will first setup a sample credential and proxy and then use this to generate the scripts.

Create Sample Credential and Proxy

We will first create a credential and proxy that we can then use to generate the creation scripts. We will use this link from Microsoft on how to create a proxy with credentials and then we will show the output of the script. The script from Microsoft is as follows. If you want to do this on your server, you will need to modify the IDENTITY value in the script for a valid account on your system.

-- creates credential CatalogApplicationCredential  
USE msdb;  
GO

CREATE CREDENTIAL CatalogApplicationCredential WITH IDENTITY = 'REDMOND/TestUser', SECRET = 'G3$1o)lkJ8HNd!';  
GO  

-- creates proxy "Catalog application proxy" and assigns the credential 'CatalogApplicationCredential' to it
EXEC dbo.sp_add_proxy  
    @proxy_name = 'Catalog application proxy',  
    @enabled = 1,  
    @description = 'Maintenance tasks on catalog application.',  
    @credential_name = 'CatalogApplicationCredential' ;  
GO
  
-- grants the proxy "Catalog application proxy" access to the Operating System (CmdExec) subsystem.  
EXEC dbo.sp_grant_proxy_to_subsystem  
    @proxy_name = N'Catalog application proxy',  
    @subsystem_id = 3 ;  
GO

When this script from is run (with a valid domain user and password) it first creates the credential named "CatalogApplicationCredential" and then creates a proxy named "Catalog application proxy" and finally enables the proxy to run Operating System commands.

Generate Credential and Proxy Scripts Based on Existing Configuration

Now that we have an example credential and proxy, we can use this sample to generate the scripts so we can recreate these on another instance of SQL Server. Here is the script that will output the commands to create the credential and proxies from the system:

-- Get the credentials from sys.credentials, the password is unknown
SELECT 'CREATE CREDENTIAL '+[name]+' WITH IDENTITY='''+[credential_identity]+''',SECRET=''G3$1o)lkJ8HNd!'''
FROM [sys].[credentials]
ORDER BY [name]


-- Get the proxies from sp_help_proxy and sys.credentials
CREATE TABLE #Info ([proxy_id] INT, [name] SYSNAME, [credential_identity] SYSNAME, [enabled] TINYINT, [description] NVARCHAR(1024), [user_sid] VARBINARY(85), [credential_id] INT, [credential_identity_exists] INT)

INSERT INTO #Info 
EXEC sp_help_proxy

SELECT 'EXEC dbo.sp_add_proxy @proxy_name='''+[i].[name]+''',@enabled='+CAST([enabled] AS VARCHAR)+',@description='+(CASE WHEN [description] IS NULL THEN 'NULL' ELSE ''''+[description]+'''' END)+',@credential_name='''+[c].[name]+''''
FROM #Info [i]
INNER JOIN [sys].[credentials] [c] ON [c].[credential_id] = [i].[credential_id]

DROP TABLE #Info


-- Get the proxy authorizations from sp_enum_proxy_for_subsystem
CREATE TABLE #Info2([subsystem_id] INT, [subsystem_name] SYSNAME, [proxy_id] INT, [proxy_name] SYSNAME)

INSERT INTO #Info2 EXEC sp_enum_proxy_for_subsystem
SELECT 'EXEC dbo.sp_grant_proxy_to_subsystem @proxy_name=N'''+[proxy_name]+''',@subsystem_id='+CAST([subsystem_id] AS VARCHAR) 
FROM #Info2

DROP TABLE #Info2

  • First we get the credentials from sys.credentials (note that you need to input the password the password will not be returned via the script).
  • Then we get the proxies from sp_help_proxy into a temp table to output as a command.
  • Finally we get the proxy authorizations from sp_enum_proxy_for_subsystem into a temp table to output as a command.

Here is the output it produces after it is run:

CREATE CREDENTIAL CatalogApplicationCredential WITH IDENTITY='REDMOND/TestUser',SECRET='G3$1o)lkJ8HNd!'

EXEC dbo.sp_add_proxy @proxy_name='Catalog application proxy',@enabled=1,@description='Maintenance tasks on catalog application.',@credential_name='CatalogApplicationCredential'

EXEC dbo.sp_grant_proxy_to_subsystem @proxy_name=N'Catalog application proxy',@subsystem_id=3

You can see, the output from the script is the same as the original provided by Microsoft, although formatted a little differenlty.

Note that you must know the password of the user specified in the credential, as there’s no official way to retrieve it. You will need to ask someone that knows the password or as a last resort, you will have to change it.

Next Steps


Last Updated: 2018-11-21


get scripts

next tip button



About the author
MSSQLTips author Pablo Echeverria I've worked for more than 10 years as a software programmer and analyst. In 2016, I switched jobs to a DBA position, where I have implement new processes, created better monitoring tools and grown my data scientist skills.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, November 14, 2019 - 2:48:43 AM - Timo Riikonen Back To Top

Here is one command set that worked for my case:

----- These commands create proxy permissions so that non-sysadm users can run SSAS analysis commands and create jobs that run them.
-- Before this, create local or domain user account for SSAS access: either with lusrmgr.msc or Control Panel\User Accounts\User Accounts. Here it has been named '.\SSASProxyAccount'
DECLARE @ProcyAccount NVARCHAR(128)
SET @ProcyAccount = CONVERT(NVARCHAR(100),SERVERPROPERTY('MachineName')) + N'\SSASProxyAccount'
SELECT @ProcyAccount 
 
-- Give sysadm permission to this account on SSAS cubes with SSMS.
 
-- We must have an external user account. This account is used to manage
-- You must replace 'SERVER_OR_DOMAIN\SSASProxyAccount' with the ProxyAccount variable content
USE msdb
-- DROP CREDENTIAL SSASCredential 
CREATE CREDENTIAL SSASCredential
WITH IDENTITY = 'SERVER_OR_DOMAIN\SSASProxyAccount',
SECRET = 'You never again need to use this password, so insert a complex password to here'
 
EXEC dbo.sp_add_proxy
@proxy_name = N'SSAS command proxy',
@enabled = 1,  
    @description = 'Proxy permissions for non-sysadmins to execute SSAS commands in SQL Server Agent',
    @credential_name = 'SSASCredential'
 
EXEC dbo.sp_grant_proxy_to_subsystem  
    @proxy_name = N'SSAS command proxy',  
    @subsystem_name = N'ANALYSISCOMMAND' ;  
 
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'SSAS command proxy', @login_name=N'TRE\sotearma'


download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools