0

currently i'm struggling with my current project. I was tasked to replace the use of Username/Password based Authentication/Connection to SQL Server (2014) and replace it with a Certificate based Authentication/Connection one. So probably my questions are:

  1. Is this possible with SQL Server ?

    • The idea is to no longer include the username/password combination to connect to the Database Instance from the Server. This then would be replaced by a certificate where, ideally would hold all the login credentials (encrypted). So in simpler terms, every pc that will use the system will need to be issued a certificate to connect to the database.

  2. If possible, is this a Single Certificate for All or One Computer - One Certificate ?

hoping to get some good/bad opinions here. thank you

Improve this question

2 Answers 2

Reset to default
2

SQL Server's equivalent option to certificate authentication is called Integrated Security.

When you use integrated security across machines, where the server lives on a different computer from the client, you also rely on an Active Directory domain to mediate trust between the machines.

It works like this:

A user logs in to a client computer joined to an Active Directory domain. As part of the authentication process, a domain controller (DC) from the domain will issue an authentication token for the session.

The user then wants to log into SQL Server using integrated security, where the SQL Server installation is a member of the same domain. To do this, the token is passed to SQL Server, which in turn validates the token against a domain controller to decide whether to grant access. Now SQL Server can trust the user is who they say they are. From there it consults it's own user information to decide whether or what access to grant.

The integrity of this process is guaranteed using – you guessed it – certificates. Also a clock (thanks Kerberos).

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Old now, but there wasn't a good answer here otherwise.
Thats all well and good, but how would you set this up in sql server ? and what does it look like in client applications ??
On the database, you join the server to the domain and enable Windows authentication. Then you add your domain users (or groups, which is much faster) as logins and grant them permissions as you would any other user in the server. On the application side, you run the application as correct domain user (either by logging into Windows in the first place, setting the correct user for the scheduled task or service, or using something like ASP.Net impersonation . Then you use the Trusted Connection option in the connection string.
@JoelCoehoorn But he specifically said 'certificate based'. Windows-based is following the logged in user and is something else entirely, right?
Windows authentication uses certificates to validate the authentication tokens.
1

What Certificate are we talking about One coming from a Root Certificate Authority In an Active Directory Network or a webpage using its SSL Cert to Authenticate back into the SQL Server? Or is this a Certificate that was generated solely for this purpose? What language/program is trying to Use the Certificate to Authorize. Im Assuming C# but I just want to make sure.

Improve this answer

3 Comments

i don't really understand but it's something that will be issued by the Server where the Database is running, so probably the Root Certificate Authority In an Active Directory Network will count. And yes, i'm currently utilizing C# for this matter. I'm hoping for something like a unique certificate as per client that will connect to the database
C# and SQL currently really only allow User/Pass Authentication however from what I have read so far you can push the database to Only allow connections with a valid Certificate. So im thinking you could make it so the Username and Password is a standard SQL Access Account but that account isn't allowed to login until a Verified Certificate is found between both devices. But from what I understand you cant Authenticate to SQL without the Username and Password.
i see, maybe i'll try and work that idea around. probably the same logic that wraps your comment, but maybe a little extended approach. for now i do not know, i'll do some more research. thank you for your answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.