Logins / Credentials / Proxies

Logins/Credentials/Proxies enforce the principle of least privilege.

Recently, we have built three production servers (SQL Server 2012) for Datawarehouse Team.

• SQL Server 2012 Enterprise Edition
• CU3
• SSIS Catalog is installed and configured

Datawarehouse Team built SSIS packages and imported to SSIS Catalog. SSIS packages which are imported in SSIS Catalog are scheduled in SQL Agent. They are run as a windows Integrated Security. Note :A service account running SQL Server service and SQL Agent has sys admin right.

We, DBAs need to change the SSIS job. We will need to use a proxy account which is mapped to a credentital.

I received my information from Jason Strate's web site.

http://www.jasonstrate.com/2013/07/security-questions-logins-credentials-and-proxies/

The next question in the list is:

What is the relationship between logins, credentials, and proxies? And why they were introduced?

Before we get too deep, though, lets defined each of these objects. They are:

• Login: A login is any principal that is granted access to a SQL Server instance. The access can be granted to domain users, domain group, or SQL Server authenticated accounts.
• Credential: A credential provides a mechanism to store login information for a domain account within SQL Server. The credential can then be used to pass that authentication information from into another login or a proxy to grant it permissions to resources external to SQL Server.
• Proxy: A proxy is used by SQL Server Agent to map credentials to SQL Server Agent subsystems (i.e. PowerShell or SSIS).

Ok. In our case,

• Create a login which is a domain account and has a public role to server. It will have db_reader, db_writer, db_ddl rights to database level. (It depends on Data FLow)
• Create a credential which maps to the domain login
• Add the credential to the domain account in login
• Create a proxy which is mapped to the credential in SSIS Package Execution under SQL Agent

SQL Server Management Studio

 Create a Login

First, a domain account login needs to be created. Second, a credential is created using the domain login. Last, add the credential to the login.

 Give public role to the domain account login to SSISDB

Create a Credential

Create a Proxy

 Create Principals

 Msdb role principals are added to the proxy account.  

1. SQLAgentReaderRole
2. SQLAgentUserRole
3. SQLAgentOperatorRole

 

A new proxy account is created

SSIS Catalog Configuration

We gave a public role to the domain account login to connect msdb and SSISDB databases. In order to give access to the proxy account from SQL Agent to SSIS packages stored in SSIS Catalog, we will need to add permission on SSIS packages in SSIS Catalog. In order to do this, Click on a project folder in SSIS Catalog.

Give the folowing permission to the domain account.

• Read, Read Object, Execute Object

Creating a job and run a job as a proxy account

• Create a job in SQL Agent
• In steps, create a step and choose run as a proxy account.

Let the job ran, open a new query window and execute the following.

usessisdb

select  * from catalog.executions

In the query result, look at a column " executed_as_name" which shows the domain account used in credential. So we can confirm that the job is executed by a proxy account which is mapped to the domain account.