Search results matching tags 'SQL Server' and 'programming'

Sell Yourself! Presentation

Mike C — Sun, 05 Dec 2010 23:01:00 GMT

Thanks to everyone who attended my "Sell Yourself!" presentation at SQLSaturday #61 in Washington, D.C., and thanks to NOVA SQL for setting up the event!

I'm uploading the presentation deck here in PDF, original length, with new materials (I had to cut some slides out due to time limits). This deck includes a new section on recruiters and a little more information on the resume.

BTW, if you're rewriting your resume I highly recommend the book Elements of Resume Style by S. Bennett. I've used it as a reference when rewriting my resume and when helping others, and it's a very valuable tool. There are one or two things I disagree with the author about (he recommends against the use of bulleted lists in the resume, I think they're great for emphasis and readability in certain areas so long as they're not overused, for instance); but overall the book has plenty of solid advice on how to get the most out of your resume.

Also, if you haven't done so yet, check out Steve Jones' presentation "The Modern Resume: Building Your Brand" and his new blog: http://modernresume.blogspot.com/. Steve is a SQL MVP, entrepreneur (a founder and editor-in-chief at SQL Server Central: http://www.sqlservercentral.com), SQL Server guru, and all-around great guy. His new career-oriented professional-development blog and presentations are full of great career advice and tips for SQL Professionals.

T-SQL Tuesday #006: Tiger/Line Spatial Data

Mike C — Tue, 11 May 2010 23:11:00 GMT

This month’s T-SQL Tuesday post is about LOB data http://sqlblog.com/blogs/michael_coles/archive/2010/05/03/t-sql-tuesday-006-what-about-blob.aspx.

For this one I decided to post a sample Tiger/Line SQL database I use all the time in live demos. For those who aren't familiar with it, Tiger/Line data is a dataset published by the U.S. Census Bureau. Tiger/Line has a lot of nice detailed geospatial data down to a very detailed level. It actually goes from the U.S. state level all the way down to street, feature and landmark level. Tiger/Line data is very complete and detailed--but the best part is it's FREE. There are lots of applications for Tiger/Line, like national [U.S.] and local mapping and geocoding applications [applications that convert street addresses to (latitude, longitude) coordinates]. All this great data is distributed in the form of a ton of ESRI shapefiles.

A shapefile is basically a file format that contains shape objects like points, lines and polygons. SQL Server doesn’t natively understand ESRI shapefiles, but it also stores geospatial objects like lines, points and polygons. There some handy utilities out there for loading these files into SQL Server. Morten Nielsen has a great utility for loading shapefiles into Geometry and Geography data types at http://www.sharpgis.net/page/Shape2SQL.aspx. Because of the volume of data involved in this project (I loaded hundreds of shapefiles) I decided to roll my own small set of SSIS custom components that read ESRI shapefiles and convert them to SQL Server spatial data types [keep an eye out -- these components are scheduled to be published with source code by SQL Server Standard magazine in the near future].

The sample database can be downloaded from http://www.sqlkings.com/downloads/Tiger_Sample.zip

Download the ZIP file from http://www.sqlkings.com/downloads/Tiger_Sample.zip.
Extract the database backup file and restore it to a SQL Server 2008 instance.

In the future I’ll be sharing some code samples on the blog to demonstrate Tiger/Line data (as well as spatial data from other sources) based on this database.

Here are a couple of quick queries you can run against this sample database to view the spatial data in SSMS 2008.

SELECT *

FROM Tiger.State;

SELECT c.*

FROM Tiger.State s

INNER JOIN Tiger.County c

ON s.STATEFP = c.STATEFP

WHERE s.STUSPS = 'TX';

SELECT *

FROM Tiger.ZCTA z

WHERE z.ZCTA5CE00 LIKE '0[7-8]%';

This last one works because all of the ZIP Code tabulation areas for the state of New Jersey start with '07' and '08'. There are similar relationships between other ZCTA prefixes and their states.

Next time we'll look at using SQL Server-based spatial data with online mapping programs like Bing maps.

*For more information about Tiger/Line data visit http://www.census.gov/geo/www/tiger/tgrshp2009/tgrshp2009.html.

It's Official - SQLSaturday is Coming to NYC!

Mike C — Sat, 06 Feb 2010 19:59:00 GMT

New Jersey SQL Server User Group (NJSQL) is bringing SQLSaturday #39 to NYC on April 24, 2010! The free all-day training event will be hosted by Microsoft at their Midtown Manhattan offices. The speaker line-up is growing fast—if you'd like to present, visit the event's open call for speakers. This is a free full-day training event, but registration is required to attend. Seating is limited.

Registration, speaker, and sponsorship details are posted at http://www.sqlsaturday.com/39/eventhome.aspx.

Encrypt it in .NET/Decrypt it on SQL Server?

Mike C — Fri, 29 Jan 2010 16:52:00 GMT

A common question on the newsgroups is "how do you encrypt data in a .NET [or other] client application and then decrypt it on SQL Server [or vice versa]?" I actually ran down my list of answers to someone who asked this in the newsgroups a few weeks ago. I won’t get into the details, but the answers all pretty much say the same thing -- theoretically you could make it work (with a lot of assumptions on your part), but it won’t be easy -- and probably not worth the investment of time and energy, to be honest. Now it’s time to change my answer.

You see, when this question is brought up the people who ask usually make a specific point to ask about symmetric encryption (AES, Triple DES, etc.). You can’t easily make the “encrypt on client/decrypt on server” scenario work with symmetric encryption because SQL Server doesn’t let you import or export symmetric keys.

Asymmetric encryption is an entirely different beast. Someone asked about sending a password to SQL Server securely (not in plain text) for FIPS compliance here.

Since passwords are usually pretty short I told the poster asymmetric encryption might solve his problem. Then I decided to prove it. The code below (both T-SQL and .NET) demonstrates. All of the steps should be performed in order. The .NET code at the end needs to be put into a C# Windows Forms or Console project of your own (.NET 2.0 or higher only).

1) T-SQL: Create a test database, database master key, and certificate on SQL Server

-- Create a test database

CREATE DATABASE Test;

-- Switch to the new test database

USE Test;

-- Create database master key

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'P@$$w0rd';

-- Create a test certificate

CREATE CERTIFICATE TestCert

WITH SUBJECT = 'Test Certificate',

EXPIRY_DATE = '20151231';

-- This statement just tests the new certificate to make sure

-- it's installed correctly

SELECT ENCRYPTBYCERT(CERT_ID(N'TestCert'), 'abcdef')

2) T-SQL: Backup the certificate (public key only) to a .cer file in the file system

USE Test;

-- Backup the certificate to a .CER file; assumes c:\Temp

-- directory exists

BACKUP CERTIFICATE TestCert

TO FILE = 'c:\Temp\TestCert.cer';

3) T-SQL: Create a stored procedure that uses the certificate to decrypt a binary string passed into it

-- This procedure uses the SQL certificate to decrypt the

-- encrypted password

CREATE PROCEDURE dbo.DecryptPasswordWithSqlCert

@EncryptedPassword binary(128)

BEGIN

SELECT CAST

(

DECRYPTBYCERT

(

CERT_ID('TestCert'),

@EncryptedPassword

) AS nvarchar(100)

) AS DecryptedPassword;

END;

4) .NET: Create an X509Certificate2 object and use the public key to encrypt a string password; Call the stored procedure with the encrypted password and use the SQL Server certificate to decrypt it

// Load the certificate from the file system and create an RSACryptoServiceProvider

// from the certificate Public Key to encrypt data

private RSACryptoServiceProvider GetCryptoProvider

(

string CertificateFilename

)

{

X509Certificate2 cert = new X509Certificate2(CertificateFilename);

RSACryptoServiceProvider r = (RSACryptoServiceProvider)cert.PublicKey.Key;

return r;

}

// Encrypts string password (Unicode) with the RSACryptoServiceProvider

private byte[] EncryptPasswordWithFileCert

(

RSACryptoServiceProvider Rsa,

string Password

)

{

// Results of RSA encryption are limited to 128 bytes

byte[] Bytes = Rsa.Encrypt(Encoding.Unicode.GetBytes(Password), false);

byte[] Result = new byte[128];

// Need to reverse the order of the encrypted bytes for SQL Server encryption

for (int i = 127; i >= 0; i--)

{

Result[127 - i] = Bytes[i];

}

return Result;

}

// Connects to server/database and executes stored procedure

// The stored procedure decrypts the encrypted password you pass in

private string DecryptPasswordWithSqlCert

(

string ConnectionString,

byte[] EncryptedPassword

)

{

string DecryptedPassword = "";

using (SqlConnection Con = new SqlConnection(ConnectionString))

{

Con.Open();

using (SqlCommand Cmd = new SqlCommand("dbo.DecryptPasswordWithSqlCert", Con))

{

Cmd.CommandType = CommandType.StoredProcedure;

// Pass in the encrypted password

Cmd.Parameters.Add("@EncryptedPassword", SqlDbType.Binary, 128).Value = EncryptedPassword;

// Return the decrypted password as a string

DecryptedPassword = (string)Cmd.ExecuteScalar();

}

return DecryptedPassword;

}

// This is my connection string

private string SqlConnString = "DATA SOURCE=(local);INITIAL CATALOG=Test;INTEGRATED SECURITY=SSPI;";

private void QuickTest

{

// Create RSACryptoServiceProvider from .cer file

RSACryptoServiceProvider Rsa = GetCryptoProvider("C:\\Temp\\TestCert.cer");

// Encrypt the password with the file certificate public key

byte[] EncryptedPassword = EncryptPasswordWithFileCert(Rsa, "Test*Password123");

// Decrypt the password on the server

string DecryptedPassword = DecryptPasswordWithSqlCert(SqlConnString, EncryptedPassword);

// Output the decrypted password

Console.WriteLine(DecryptedPassword);

}

A couple of items worth noting about this code:

* SQL Server (and .NET) asymmetric encryption function have a strict limit of 128 bytes that can be returned by the encrypted result. The encryption functions add 11 bytes of padding, so you’re automatically down to 117 bytes of plain text that can be encrypted or 58 Unicode characters. You can work around these limitations by encrypting your data in chunks, but I wouldn’t advise it -- asymmetric encryption is expensive in terms of time and resources.

* For some reason SQL Server needs the .NET asymmetric encryption results reversed, byte-for-byte. Not sure of the exact reason for this, but it’s simple enough to handle (as I did in the code) with a for loop on the .NET side.

* The BACKUP CERTIFICATE statement in the sample code only exports the certificate Public Key, which is used for encryption. You can also export the Private Key (for decryption) if you wish, but there’s no need in this scenario. You’ll need to look up the syntax of the BACKUP CERTIFICATE statement in BOL if you need to export your certificate’s Private Key.

* The .NET X509Certificate2 class is used in the code sample, and it is only supported on .NET 2.0 and higher. The older .NET X509Certificate class won’t do the job because it is lacking some features that this code sample requires.

Anatomy of a Geek

Mike C — Fri, 02 Oct 2009 14:45:00 GMT

I just got tagged by my old friend Jacob Sebastian at Beyond Relational, and it turns out it's my turn to explain how I became such a geek :)

Way back in the day I got my start on the TRS-80 Model III (affectionately known as the "Trash-80s"), a nice little Z-80 based desktop computer with an 80x25 monochrome screen and a whopping 64KB of memory. When I say "desktop", I mean it could fill an entire desktop :) After playing some games written in BASIC on it I started getting curious about what made the games work. So I started reverse-engineering them and eventually got to where I could actually write my own games on it. Around that same time I got to start writing code on a Commodore 64 (oh, the colors and sounds!) Of course creating database applications on a computer with 64KB of memory, a processor designed to run toaster ovens and a tape drive proved a little more challenging than I cared for :)

Fast-forward a few years - I started writing Turbo Pascal ("terrible Pascal" anyone?) programs on PC compatibles (the ones with the incredible 16 color palette -- only 4 colors viewable at any one time...) And my memory suddenly spiked up to 640KB ("no one will ever need more" -- I still believe it!) I actually wrote several increasingly complex homemade database management programs myself during that time, until I finally got my hands on a copy of dBase and realised someone else had already built the database engine. Talk about an epiphany--suddenly I could concentrate on designing databases and not database engines!

Around 1995 I was introduced to the world of relational databases with SQL Server 6.0. Since then I've been plugging away at SQL and learning new things with each new release. I'm still fascinated at the potential of database systems and where we're headed.

I guess my best advice to anyone who wants to be a geek would be explore all the interesting code samples you can find and don't get discouraged, even when your code breaks on the first 99 runs :)

To continue the chain I'd like to tag a couple of folks that I'm personally interested in finding out about their start in this field:

* SQL Server guru extraodinaire, Adam Machanic
* THE Powershell Grandmaster (and Bill Gates body double), Allen White

"Cloning" Symmetric Keys

Mike C — Thu, 18 Jun 2009 01:40:00 GMT

It's well-known by now that SQL Server 2005 and 2008 include new encryption-related statements that allow you to create and administer encryption keys. You can use CREATE CERTIFICATE to create or import a certificate or DROP ASYMMETRIC KEY to remove an asymmetric key from the database, for instance. One of the interesting ommissions from the T-SQL encryption statements is the statements necessary to backup and restore a symmetric key. Why would you want to do this? I can think of a couple of reasons off the top of my head:

You need to backup symmetric keys (and all other encryption keys, in fact) as part of an overall disaster recovery (DR) program. If a server needs to be rebuilt you obviously need a way to restore all encryption keys.
You need to implement the same symmetric keys on multiple servers. There could be a couple of reasons for this -- you might encrypt data on one server and decrypt it on another, or you might be load-balancing across a server farm and need identical encryption keys on multiple servers simultaneously.

It seems like a bit of an oversight to not include BACKUP and RESTORE SYMMETRIC KEY options in T-SQL, but in practice you can effectively achieve the same end results with the standard CREATE SYMMETRIC KEY statement. Basically the CREATE SYMMETRIC KEY statement gives you an option to "clone" the exact same symmetric key on any SQL Server 2005 or 2008 instance, anywhere, at any time. To create a cloneable symmetric key you need to specify two special CREATE SYMMETRIC KEY options:

The IDENTITY_VALUE option, which SQL Server uses to generate a GUID (uniqueidentifier) for the key
The KEY_SOURCE option, which SQL Server uses as key material to generate the actual key

As long as you specify the same values for the IDENTITY_VALUE and KEY_SOURCE options (and the same ALGORITHM), your symmetric key will be exactly the same no matter where, when, or how many times you create it. To be honest, if I were creating a list, always use IDENTITY_VALUE and KEY_SOURCE would be listed in the top 10 list of SQL Server encryption best practices.

Here's a quick sample demonstrating the CREATE SYMMETRIC KEY statement with IDENTITY_VALUE and KEY_SOURCE specified:

CREATE SYMMETRIC KEY test_aes128_key
WITH KEY_SOURCE = 'I am the very model of a modern major general',
IDENTITY_VALUE = 'E pluribus unum',
ALGORITHM = AES_128
ENCRYPTION BY PASSWORD = 'p@$$w0rd';

This CREATE SYMMETRIC KEY statement will create the same symmetric encryption key on any SQL Server 2005 or 2008 instance on which you run it. This brings up another point, about security. If the same KEY_SOURCE and IDENTITY_VALUE options can create the exact same encryption key on any of your servers, they will create the exact same encryption key on any of my servers, or any server owned by any hacker anywhere in the world. So once you've run your CREATE SYMMETRIC KEY statement, the IDENTITY_VALUE and KEY_SOURCE need to be handled like any other secure information. Don't leave them lying around where just anyone can access them. Store them with your certificates, key backups, and other confidential materials in a secure off-site location.

So what happens when you don't specify the IDENTITY_VALUE and KEY_SOURCE options? Well, basically SQL Server generates an unpredictable GUID to identify the symmetric key and the encryption key source material is randomly generated. Basically you'll never regenerate the exact same key again. Ever. There could be situations where this would be handy. The concept of "session keys" comes to mind. A session key is basically a "temporary" key that's only required to encrypt data for a user during a single session. Since it only exists for the life of the session, a totally randomly-generated key is just fine.

For my tastes, it would make more sense to require IDENTITY_VALUE and KEY_SOURCE options by default. If you didn't want to specify both of these options there should be another option/indicator specifically to say that you want these options generated randomly. At any rate, it's a good idea to get into the habit of treating these options as if they are mandatory unless they have a very specific special-purpose requirement (e.g., "session keys").