What is the purpose of creating a login from a certificate?

sql-server authentication ssl-certificate client-certificates

12,337

It is used for code signing SQL Server database objects. The CREATE LOGIN x FROM CERTIFICATE y is fully explained here along with examples and scenarios for SQL Server 2008 and 2008 R2: http://technet.microsoft.com/en-us/library/ms345102(v=sql.105).aspx

Here's a great example of signing a stored procedure in SQL Server 2014: http://technet.microsoft.com/en-us/library/bb283630.aspx

12,337

Author by

Disillusioned

Why am I disillusioned? In my opinion, the whole stack exchange network has started to rot. Moderators seem to be incapable of differentiating between abusive behaviour and defensive behaviour. As a result, they have been allowing abusers to run rampant carrying out their attacks unchallenged. I personally have observed and experienced numerous instances where moderators actively participate in abuse against users merely trying to protect themselves from the abusers. This combined with the company's promulgated efforts to retain users seems to have resulted in a misguided desire "protect" new users at all costs. When new users come to the expecting and even demanding "service", they're not to be taken to task. Instead of teaching new users when they do wrong, the effort is to molly-coddle them regardless of negativity they bring. As a result the network is likely to breed a caucus of retained users who have never learnt to improve their own behaviour. Sadly I have serious concerns that a once-great network of sites is headed to self-destruction.

Updated on June 05, 2022

Comments

Disillusioned almost 2 years
SQL Server provides the option to create a login from a certificate. E.g.
```
USE MASTER;
CREATE CERTIFICATE <certificateName>
    WITH SUBJECT = '<loginName> certificate in master database',
    EXPIRY_DATE = '12/05/2025';
GO
CREATE LOGIN <loginName> FROM CERTIFICATE <certificateName>;
GO
```
However, the documentation states (emphasis mine):

Logins created from certificates or asymmetric keys are used only for code signing. They cannot be used to connect to SQL Server. You can create a login from a certificate or asymmetric key only when the certificate or asymmetric key already exists in master.

What is the point of creating a login that cannot be used to connect to the server?

Background

We need to make some security changes to our middle-tier application. In particular, how it connects to its database. The powers that be have decreed that: "we cannot use Windows authentication".
I know this would normally be the preferred method, but anyone who has access to the middle-tier servers would have the same access to the database as the application.

If we use SQL authentication, we would need to:
- Encrypt the password.
- Store it in a config file/registry.
- Then decrypt the password when the application needs to connect to the database.
While the above is possible, I was somewhat hoping that:
- I could use a certificate based login.
- Associate the certificate with the application during the build process.
- And use that to connect to SQL Server.
The above mentioned extract from the documentaion seems to contradict this.
MrBoJangles almost 10 years

Maybe add a little summary about the link so as to avoid the short-length hobgoblins.
quest4truth over 7 years

These links do not answer the question. These pages show the creation of certificate logins, but they still use non-certificate logins to actually sign the code. 1. A certificate is required to sign code, but a certificate login is not. 2. I can't connect to the database with a certificate login. 3. The examples actually show how to sign the code without the certificate login. 4. The examples don't make it clear how to use a certificate login to sign a database object. The question remains, what is the purpose of a certificate login?
TrevorBrooks over 7 years

@quest4truth they do explain why, maybe try reading the articles.
quest4truth over 7 years

Yes I read the articles. Better than reading the articles, I actually tried the code, with and without the certificate login. Works both way. I'm not ruling out that I could have missed something. If you discerned something I missed, just explain it to me in English. Thanks
TrevorBrooks over 7 years

From the first technet article: "Logins created from certificates or asymmetric keys are used only for code signing." What is code signing? From Wikipedia: "Code signing is the process of digitally signing executables and scripts to confirm the software author and guarantee that the code has not been altered or corrupted since it was signed. The process employs the use of a cryptographic hash to validate authenticity and integrity." Does this help?
quest4truth over 7 years

I understand all of that. What I don't understand is how to actually use the certificate login to sign the module. The page plainly states that it can be done, as you've pointed out, but the sample code doesn't demonstrate it. Notice that login_SysProcCert is not actually used for anything but a security boundary in the sample code, and that if you delete every line which refers to it, everything works.
TrevorBrooks over 7 years

Have you referenced this? msdn.microsoft.com/en-us/library/ms181700.aspx
quest4truth over 7 years

Yes I was just looking at that. I think I understand it now. A certificate user is not used for module signing. That's what the certificate is for. A certificate user is a security tool. You can invest privileges into this otherwise unusable principal which then transfer to objects that you sign with that certificate. It's wrong to say "logins created from certificates are used for code signing". They're not. They can't be.
TrevorBrooks over 7 years

You should definitely let Microsoft know so that they can update their documentation with your important findings.
quest4truth over 7 years

Well, that's why I was seeking clarification. If it doesn't work that way, don't say it that way.
LearnByReading over 7 years

@quest4truth i found the comments more educational and informative than the answer. I am not sure if TrevorBrooks is being sarcastic or not (about letting MS know). I am confused now, so you are sure that certificate users cannot be used for mod signing? What are they used for then? You said it's a "security tool", to do what?
quest4truth over 7 years

Yes, I'm sure. Certificates are used for mod signing, and security principals like users and logins are used for access. You can't sign anything without a certificate, and a certificate cannot function like a security principal. What you do is create a certificate user. Then the certificate user is authorized to those objects which are signed by the certificate. That authorization doesn't do him any good because he is inoperable, but he functions as an ownership chain so that if you grant someone else permission to the end of the chain he has access to all of it. Only a security tool
LearnByReading over 7 years

@quest4truth I am not exaggerating when I say that I spent 12 hours on this and your simple comment just explained it all. I owe you one! Thank you. If you can recommend me some learning materials, I'd be even more grateful. I've spent 12 hours going through Online Book/Tutorials, but they are all useless because they just give you examples and cases, whereas I don't even understand the concept. I find online sources to be full of examples, devoid of principles/concepts. Thanks again
quest4truth over 7 years

Well, you're welcome and I'm glad I could help. I don't know where to direct you because, as pointed out, the Microsoft documentation is wrong. I spent a lot of time researching as well.