As I mentioned in the introductory post, during the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer.  Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic.  Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

Security question

The next question in the list is:

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

This is another question that I like because a lot of people only know about logins.  If they know about credentials and proxies, they aren’t sure where and when they should be used.  At a high level, each of these allow permissions to be assigned to a process or person, and they are not interchangeable.

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).

Now that we’ve defined the objects, let’s look at how they can be used.  To start, if you need to login and access a SQL Server instance, you’ll just use a login.  Regardless of your needs, if you need to get in and do work, the login is through route to go.  The other two won’t help you out at all.

Credentials get brought in when permissions from domain users need to be granted to accounts or services that wouldn’t usually have those permissions.  For instance, if an assembly required EXTERNAL_ACCESS, those permissions could be granted through the use of a credential.  Credentials can also be used to grant SQL Authentication accounts access to external resources.

Proxies allow credentials to be brought into SQL Agent job steps and for those steps to inherit new permissions.  For instance, the SQL Server Agent service account may not have permission to access a remote server, but a proxy could inherit those permissions from a credential to allow the step to access the directory.

In my experience, the use of credentials and proxies isn’t too wide reaching.  A great example of using them in action can be found in my blog post Troubleshooting Permission Issues With Credentials.

Summary

Logins, credentials, and proxies are useful and interesting beasts.  If you want to properly secure your SQL Server environment, you’ll need more that logins to get the job done and you’ll likely leverage credentials and proxies.  Doing so helps to minimize the permissions required for the SQL Server Agent service account.  It also helps enforce the principle of least privilege.

And when it comes to figuring why an SSIS package works for a work station and not on a production server… it’s a great tool for sticking it to the developers… or the DBA… depending on your perspective.

When have you used proxies and credentials in the past?  Or do you use them?