Archive

Archive for the ‘Security’ Category

Use Windows Authentication for SSMS as Domain User from Non-Domain Account

February 26th, 2011 1 comment

For those that work with Active Directory environments, but (for whatever reason) prefer to keep their computer off the domain (consultants — that’s probably you!), you DO have the option to run SQL Server Management Studio using Windows Authentication from your non-domain user account. I only recently discovered this, and I can’t believe it took me this long to find it.

SQL Server Management Studio 2008:
runas.exe /netonly /user:MYDOMAIN\myuser "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

SQL Server Management Studio 2005 Express:
runas.exe /netonly /user:MYDOMAIN\myuser "C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\ssmsee.exe"

You get the idea :)

Now when you run Management Studio, you can select ‘Windows Authentication’ to login to your target SQL Server using your domain credentials. No need to use RDP to remote to your SQL Server or running it in a Domain-enabled Virtual Machine!

Generate a PKCS #12 (PFX) Certificate from Win32 CryptoAPI PRIVATEKEYBLOB

July 30th, 2009 No comments

We had an accounting system that used a Microsoft Win32 CryptoAPI blob to encrypt/decrypt credit card information for recurring customer information. It was time for an upgrade to .NET land. Keith, the lead developer for this project, decided it would be beneficial to switch to x509 certificates for improved key management (and I wasn’t going to argue).

So what we physically used to encrypt/decrypt cards in the legacy system was a PRIVATEKEYBLOB and our ultimate goal was to use a certificate in the PKCS #12 format. My system at the office is Windows XP, and I wanted to use OpenSSL to accomplish the task of converting the private key blob to something more suitable for our new system, but I didn’t want to transmit any of our top secret keys across the VPN or even across the network for that matter.

OpenSSL did not begin supporting PRIVATEKEYBLOB as an acceptable format until 1.0.0 Beta, but 0.9.8h was the only Windows binary readily available. So I grabbed the OpenSSL source (here) and compiled it using GCC within Cygwin. If you don’t have Cygwin (get it here), it’s very easy to get started, and you can select from a large variety of Linux packages during setup. So, during setup, look for GCC and make sure you enable it.

Here’s how to compile OpenSSL 1.0.0 Beta on your native Linux environment or with Cygwin:

[code lang="bash"]$> cd /usr/local/
$> wget http://www.openssl.org/source/openssl-1.0.0-beta3.tar.gz
$> tar -xzf openssl-1.0.0-beta3.tar.gz
$> cd openssl
$> ./config && make && make install && make clean[/code]

If something broke during install, check the online docs, or re-run Cygwin setup to make sure you selected the gcc toolset. I’ll assume from this point forward you are using OpenSSL 1.0 in either a native Linux or a Cygwin environment. If you aren’t sure, start OpenSSL and type “version” to check your ::drumroll please:: version number.

Let’s get started.

The OpenSSL command below will take your PRIVATEKEYBLOB and output an RSA private key in PEM format. Please note the use of “MS\ PRIVATEKEYBLOB” instead of the alternative “PRIVATEKEYBLOB”. Backspace is required to escape the blank space after “MS” in Linux when passed as a parameter on the command line. So, if all goes well, you should have a PEM file. If it doesn’t, try specifying a different input form (e.g. DER or PRIVATEKEYBLOB instead of MS\ PRIVATEKEYBLOB).

[code lang="bash"]$> openssl rsa -inform MS\ PRIVATEKEYBLOB -outform PEM -in private.pvk -out private.pem[/code]

Now that we have a PEM file with an RSA private key, we can generate a new certificate based on that private key (command below). This will generate an x509 certificate valid for 5 years. Once you run this you’ll be prompted with the usual country/state/city/company information, but what you specify there is up to you. I would recommend adding a passkey when it prompts you at the end

[code lang="bash"]$> openssl req -new -x509 -key private.pem -out newcert.crt -days 1825[/code]

If all continues to go well, you should have a private key in PEM format and your brand new certificate. One last command is needed to generate the PKCS #12 (aka PFX) certificate bundle.

[code lang="bash"]$> openssl pkcs12 -export -in newcert.crt -inkey private.pem -name "My Certificate" -out myCert.p12[/code]

If you didn’t receive any errors, then congratulations! You can now import this PKCS12 bundle into any Windows certificate repository and no longer need to hard code blobs into your code.

Hope this helps save someone a few hours time.