none
Is there a CLI way of adding a certificate to an MsSql server?

Réponses

  • Hi Alex,

    Got it. Thanks for clarification. You mentioned creating a cert in .NET, so I thought CLR and SQL Server certs that you'd find in databases. You REALLY DID mean CLI as "command line interface". ;-)

    So, you want to programmatically configure an SSL Certificate for use of a SQL Server instance. That's WMI programming (normally configuration = WMI, internal database objects = SMO). There are a few ways of doing what you want to do (naturally), but you can't use OSQL or SQLCMD to do it. Another question to consider is: Are you using SQL Server 2005 and above? If so, I'd use PowerShell rather than cmd.exe.

    If you start with a certificate that is valid for SSL, you need to accomplish three things:

    1. Install certificate in appropriate place in certificate store (WMi)
    2. Make sure its accessible by the SQL Server service account and that's its the right kind of cert for SSL (Security)
    3. Install it into a SQL Server instance (WMI)

    You have a choice of:

    1. cmd.exe and WScript programming using WMI.
    2. PowerShell.exe and programming use .NET classes and PowerShell's native WMI support.
    3. PowerShell.exe and Microsoft.SqlServer.Management.Smo.Wmi if this encapsulating library supports it. http://msdn.microsoft.com/en-us/library/ms212660.aspx

    I don't see the classes needed for choice #3 given a quick glance but sometimes it's difficult to locate what you need in the docs for this namespace.

    Have a look at this URL http://thesqldude.wordpress.com/2012/04/21/setting-up-ssl-encryption-for-sql-server-using-certificates-issues-tips-tricks/  for starters. Although it mentions a GUI, he also mentions using direct registry updates (which PowerShell does nicely and WScript supports) and also has a script for part of it (in WScript). I think PowerShell has best WMI support (and PowerShell is the way of the future), but I don't have a script for you.

    Be careful about using the appropriate WMI namespace (they change with SQL Server releases) and read Sudarshan Narasimhan's (SQL Dude's) prereq background article on installing certs for SSL. Some good info in there. I'd almost make sure you can do it with the GUI first and then proceed to translating it to command line/WMI/registry entries.

    You might get better answers about the configuration part in the "SMO and DMO" forum, but your question does encompess both security (the SSL cert part) and configuration. I'd almost be tempted to move it to SMO and DMO, but I'm not the moderator of this forum and can't do that anyway. Let us (or SMO/DMO forum) know if you get stuck.

    Hope this helps, sorry for the misunderstanding on my part. Cheers, Bob


    lundi 30 avril 2012 16:53

Toutes les réponses

  • Hi Alex.Mil

    >> Does anybody know a CLI way of adding certificates to an MsSql server?

    Based on my research, please refer to the MPLS_Mike’s reply in this thread about how to install and configure an SSL certificate.

    There are some Useful utilities and commands about generate and add a certificate as below:
    certreq.exe    - used to generate a certificate request without IIS or MS Certificate Services.
    certutil.exe    - used to obtain critical certificate information (hash) and verify installed certificates
    httpcfg.exe    - obscure mention in BOL/MSDN2; bind SSL certificate
    winhttpcertcfg.exe    - used to assign permissions to certificate store private keys if needed.

    For the steps and more information, please refer to: http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/8f27fbb3-1566-408a-bfde-abdde17fb424/

    Regards, Amber zhang


    mardi 3 avril 2012 07:00
    Modérateur
  • Hi Amber,

    Thank you for the link provided.

    Mike's explanation was very helpful and I learned a few new tricks.

    However, as far as I understand, this solution does not help me.

    As I mentioned earlier my main issue is adding the certificates to the MsSql server.

    The solution MPLS_Mike provides:

    V. Run SQL Server Configuration Manager, per BOL instructions.  The Protocols for <instance name> properties | certificate tab; the certificate should appear in the drop down box.  The certificate name will be the CN name, unless you assign a friendly name using the mmc snap-in.

    is a GUI based solution, as it utilizes "SQL Server Configuration Manager" - a GUI application.

    While I am looking for a CLI solution.

    Thanks,

    Alex


    Thanks, Alex


    • Modifié Alex.Mil mercredi 4 avril 2012 12:09
    mardi 3 avril 2012 15:14
  • Does anybody else has any idea?

    Thanks, Alex

    dimanche 8 avril 2012 11:16
  • Hi Alex.Mil,

    If the CLI solution you mentioned means  sqlcmd Command-Line. Based on my research, the sqlcmd is used to configure it to implicitly trust the server certificate without validation. This option is equivalent to the ADO.NET option TRUSTSERVERCERTIFICATE = true.

    If you want to create a new certificate on SQL Server, please use the method I mentioned above.

    For more information please refer to sqlcmd Utility http://msdn.microsoft.com/en-us/library/ms162773(v=sql.105).aspx
     

    Regards, Amber zhang

    mercredi 11 avril 2012 07:57
    Modérateur
  • Hi Alex.Mil,

    If the CLI solution you mentioned means  sqlcmd Command-Line. Based on my research, the sqlcmd is used to configure it to implicitly trust the server certificate without validation. This option is equivalent to the ADO.NET option TRUSTSERVERCERTIFICATE = true.

    If you want to create a new certificate on SQL Server, please use the method I mentioned above.

    For more information please refer to sqlcmd Utility http://msdn.microsoft.com/en-us/library/ms162773(v=sql.105).aspx
     

    Regards, Amber zhang

    Hi Amber,

    This is a nice suggestion, however I do not fully understand how it can help me.

    You mention that this command can allow me to configure the MsSql server to trust server certificates without validation. This is an interesting suggestion, but I still need to find a way to configure the MsSql server to use the said certificate (using a CLI command).

    Also, as far as I understand, the sqlcmd utility allows "enter Transact-SQL statements, system procedures, and script files at the command prompt" which is currently not an issue for me as I am already using the OSQL utility for such needs:

    http://msdn.microsoft.com/en-us/library/aa214012(v=sql.80).aspx

    Also, I found a document describing a solution close to what I need:

    http://msdn.microsoft.com/en-us/library/ms187798.aspx

    This article describes the usage of the CREATE CERTIFICATE command that can be run inside the MsSql server, which is a useful feature in itself.

    But what it doesn't describe, and what I actually need, is a command to configure the said certificate (or any other certificate) as a default certificate for any given MsSql Server.

    Do you have any idea if there is in fact such a command?


    Thanks, Alex


    • Modifié Alex.Mil mercredi 11 avril 2012 14:14
    mercredi 11 avril 2012 14:12
  • Hello everybody,

    I've waited for someone to answer, but had not luck so far.

    So i'll try one last time:

    Does anyone (especially Microsoft representatives) knows how to achieve what I'm trying to do?

    Or if it's even possible?

    (If it's impossible, I would still like to know - At least I will know not to waste my time looking in this direction)


    Thanks, Alex

    dimanche 29 avril 2012 06:52
  • I don't work for Microsoft, but do know a bit about SQL Server, and find your question difficult to understand. That's why (I think) folks are having a hard time guessing what you mean. "CLI" might refer to command-line interface (cmd.exe or powershell or SQL Server's command line interface SQLCMD). Or... .NET?  So I'll give it another try...

    If what you mean by "Is there a CLI way of adding a certificate to an MsSql server (SQL Server)?" is "Does there exist a .NET API to encapsulate T-SQL's CREATE CERTIFICATE DDL statement", that API would be SMO. SMO does have a Database.Certificates Property, which is documented with the verbiage "To add a new certificate to the collection, call the certificate constructor Certificate." It appears that you can use this constructor/class to add a SQL Server-generated self-signed certificate to a database.

    But the class doesn't appear to have a mechanism to add a certificate (i.e. someone else's certificate, rather then self-signed) from a file or stream. And if you are looking for an automated way to encapsulate DDL, SMO is usually your best (only) bet, unless you write your own library that calls the DDL using ADO.NET.

    If you can add the certificate you want (perhaps to add SSL support) using SQL Server Configuration Manager, you would likely be able to use the WMI .NET classes or SQL Server's encapsulation "Microsoft.SqlServer.SqlWmiManagement.dll". It's part of/related to SMO.

    But just because the .NET base-class libraries have a way to create certificates and SQL Server uses certificates doesn't mean the two are related. These are (although they both deal with certificates) unrelated APIs. Have a go at SMO for certificates/WMI/SMO's encapsulation of WMI. Reposting to the SMO and DMO forum might be helpful (or not).

    I hope I understood what you meant and that this was helpful,

    Cheers, Bob

    dimanche 29 avril 2012 19:19
  • I don't work for Microsoft, but do know a bit about SQL Server, and find your question difficult to understand. That's why (I think) folks are having a hard time guessing what you mean. "CLI" might refer to command-line interface (cmd.exe or powershell or SQL Server's command line interface SQLCMD). Or... .NET?  So I'll give it another try...

    If what you mean by "Is there a CLI way of adding a certificate to an MsSql server (SQL Server)?" is "Does there exist a .NET API to encapsulate T-SQL's CREATE CERTIFICATE DDL statement", that API would be SMO. SMO does have a Database.Certificates Property, which is documented with the verbiage "To add a new certificate to the collection, call the certificate constructor Certificate." It appears that you can use this constructor/class to add a SQL Server-generated self-signed certificate to a database.

    But the class doesn't appear to have a mechanism to add a certificate (i.e. someone else's certificate, rather then self-signed) from a file or stream. And if you are looking for an automated way to encapsulate DDL, SMO is usually your best (only) bet, unless you write your own library that calls the DDL using ADO.NET.

    If you can add the certificate you want (perhaps to add SSL support) using SQL Server Configuration Manager, you would likely be able to use the WMI .NET classes or SQL Server's encapsulation "Microsoft.SqlServer.SqlWmiManagement.dll". It's part of/related to SMO.

    But just because the .NET base-class libraries have a way to create certificates and SQL Server uses certificates doesn't mean the two are related. These are (although they both deal with certificates) unrelated APIs. Have a go at SMO for certificates/WMI/SMO's encapsulation of WMI. Reposting to the SMO and DMO forum might be helpful (or not).

    I hope I understood what you meant and that this was helpful,

    Cheers, Bob

    Hi Bob,

    Thanks for the reply.

    I will try to make myself clear.

    What I want to do is add a certificate to an MsSql instance. Which is done so:

    http://support.microsoft.com/kb/316898

    I however, have a restriction that prevents me from using GUI, meaning: no GUI, no mouse, no windows.

    I can only send CLI (Command Line) commands, meaning: cmd.exe or SQL Server's command line interface SQLCMD (I don't know Powershell, but I can learn if I need).

    Basically I only use cmd.exe commands, but when needed I use http://msdn.microsoft.com/en-us/library/aa214012(v=sql.80).aspx in order to run SQLCMD commands from cmd.exe.

    In conclusion: I think what I'm looking for is closer to the first interpretation that you offered than the second one (.NET)

    Hope this makes it clearer.


    Thanks, Alex


    • Modifié Alex.Mil lundi 30 avril 2012 08:08
    lundi 30 avril 2012 08:07
  • Hi Alex,

    Got it. Thanks for clarification. You mentioned creating a cert in .NET, so I thought CLR and SQL Server certs that you'd find in databases. You REALLY DID mean CLI as "command line interface". ;-)

    So, you want to programmatically configure an SSL Certificate for use of a SQL Server instance. That's WMI programming (normally configuration = WMI, internal database objects = SMO). There are a few ways of doing what you want to do (naturally), but you can't use OSQL or SQLCMD to do it. Another question to consider is: Are you using SQL Server 2005 and above? If so, I'd use PowerShell rather than cmd.exe.

    If you start with a certificate that is valid for SSL, you need to accomplish three things:

    1. Install certificate in appropriate place in certificate store (WMi)
    2. Make sure its accessible by the SQL Server service account and that's its the right kind of cert for SSL (Security)
    3. Install it into a SQL Server instance (WMI)

    You have a choice of:

    1. cmd.exe and WScript programming using WMI.
    2. PowerShell.exe and programming use .NET classes and PowerShell's native WMI support.
    3. PowerShell.exe and Microsoft.SqlServer.Management.Smo.Wmi if this encapsulating library supports it. http://msdn.microsoft.com/en-us/library/ms212660.aspx

    I don't see the classes needed for choice #3 given a quick glance but sometimes it's difficult to locate what you need in the docs for this namespace.

    Have a look at this URL http://thesqldude.wordpress.com/2012/04/21/setting-up-ssl-encryption-for-sql-server-using-certificates-issues-tips-tricks/  for starters. Although it mentions a GUI, he also mentions using direct registry updates (which PowerShell does nicely and WScript supports) and also has a script for part of it (in WScript). I think PowerShell has best WMI support (and PowerShell is the way of the future), but I don't have a script for you.

    Be careful about using the appropriate WMI namespace (they change with SQL Server releases) and read Sudarshan Narasimhan's (SQL Dude's) prereq background article on installing certs for SSL. Some good info in there. I'd almost make sure you can do it with the GUI first and then proceed to translating it to command line/WMI/registry entries.

    You might get better answers about the configuration part in the "SMO and DMO" forum, but your question does encompess both security (the SSL cert part) and configuration. I'd almost be tempted to move it to SMO and DMO, but I'm not the moderator of this forum and can't do that anyway. Let us (or SMO/DMO forum) know if you get stuck.

    Hope this helps, sorry for the misunderstanding on my part. Cheers, Bob


    lundi 30 avril 2012 16:53
  • Hi Alex,

    Got it. Thanks for clarification. You mentioned creating a cert in .NET, so I thought CLR and SQL Server certs that you'd find in databases. You REALLY DID mean CLI as "command line interface". ;-)

    So, you want to programmatically configure an SSL Certificate for use of a SQL Server instance. That's WMI programming (normally configuration = WMI, internal database objects = SMO). There are a few ways of doing what you want to do (naturally), but you can't use OSQL or SQLCMD to do it. Another question to consider is: Are you using SQL Server 2005 and above? If so, I'd use PowerShell rather than cmd.exe.

    If you start with a certificate that is valid for SSL, you need to accomplish three things:

    1. Install certificate in appropriate place in certificate store (WMi)
    2. Make sure its accessible by the SQL Server service account and that's its the right kind of cert for SSL (Security)
    3. Install it into a SQL Server instance (WMI)

    You have a choice of:

    1. cmd.exe and WScript programming using WMI.
    2. PowerShell.exe and programming use .NET classes and PowerShell's native WMI support.
    3. PowerShell.exe and Microsoft.SqlServer.Management.Smo.Wmi if this encapsulating library supports it. http://msdn.microsoft.com/en-us/library/ms212660.aspx

    I don't see the classes needed for choice #3 given a quick glance but sometimes it's difficult to locate what you need in the docs for this namespace.

    Have a look at this URL http://thesqldude.wordpress.com/2012/04/21/setting-up-ssl-encryption-for-sql-server-using-certificates-issues-tips-tricks/  for starters. Although it mentions a GUI, he also mentions using direct registry updates (which PowerShell does nicely and WScript supports) and also has a script for part of it (in WScript). I think PowerShell has best WMI support (and PowerShell is the way of the future), but I don't have a script for you.

    Be careful about using the appropriate WMI namespace (they change with SQL Server releases) and read Sudarshan Narasimhan's (SQL Dude's) prereq background article on installing certs for SSL. Some good info in there. I'd almost make sure you can do it with the GUI first and then proceed to translating it to command line/WMI/registry entries.

    You might get better answers about the configuration part in the "SMO and DMO" forum, but your question does encompess both security (the SSL cert part) and configuration. I'd almost be tempted to move it to SMO and DMO, but I'm not the moderator of this forum and can't do that anyway. Let us (or SMO/DMO forum) know if you get stuck.

    Hope this helps, sorry for the misunderstanding on my part. Cheers, Bob


    Hi Bob,

    Thanks for the explanation.

    I've been reading a bit about PowerShell and WMI...

    I don't have time to fully try it right now, but as soon as I have I promise to post whether it worked for me :)


    Thanks, Alex


    • Modifié Alex.Mil dimanche 6 mai 2012 10:33
    dimanche 6 mai 2012 10:33