Security in SQL Server 2005 – “non-traditional” logins and users

This one's been around for a while, but don't think we'd mentioned it in the book. In addition to Windows logins and SQL logins, you can have SQL logins mapped to a certificate or to an asymmetric key. Same with database users. One of the reasons you might want to do this is to implement Service Broker transport security. That is, create a certificate in master, then create a login mapped to the cert. Now use the cert in the CREATE ENDPOINT … FOR SERVICE_BROKER.

To allow your business partners to authenticate with your endpoint (in their implementation of transport security) you BACKUP the cert to a file (the default is to BACKUP public key portion only) and then send the cert to your business partner. They install it in master to use to authenticate your endpoint.

There a variation of CREATE USER foo FROM CERTIFICATE that's caused a lot of discussion on one of the newsgroups lately. That is "CREATE USER … WITHOUT LOGIN". This is used with Service Broker too, but with full dialog security, rather than transport security. This USER has only the permissions it needs (usually only SEND on the SERVICE/ or RECEIVE on the QUEUE in question). So this is used for the following scenario:

1. Create a user without a login
2. Create a cert owned by the user
3. Backup the cert (public key only) to a file, send to business partner
4. They create a user without login
5. They install your cert from a file
6. They GRANT the user the appropriate access
7. Your BEGIN DIALOG uses full dialog security (ENCRYPTION = ON is the default).

Folks on newsgroups have said it (USER WITHOUT LOGIN) is undoc'd, but I remembered seeing this before. Then I remembered where. It's in Neils' remote Service Broker examples here.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.