Search results matching tags 'ssis' and 'SQL 2008'

SQLSaturday #69 - Philly Love

Mike C — Mon, 07 Mar 2011 01:38:00 GMT

Thanks to the Philly SQL Server User Group (PSSUG) and to everyone who attended SQLSaturday #69 in the City of Brotherly Love yesterday. It was a great event with a lot of great people. My presentations are available for download at the links below:

http://www.sqlsaturday.com/viewsession.aspx?sat=69&sessionid=3333

http://www.sqlsaturday.com/viewsession.aspx?sat=69&sessionid=3334

I just went through my speaker evaluations, and I'm happy to report the response was pretty positive across the board. Having lived in Philly, I know Philadelphians aren't shy about telling how they really feel, so I really appreciate the positive feedback.

For those of you who wrote comments with areas for improvement, rest assured I appreciate the feedback and I'll work your suggestions into future presentations!

Next stop is SQLSaturday #71 in Boston (http://www.sqlsaturday.com/71/eventhome.aspx). Lots of top-notch speakers presenting at this one, and I'm looking forward to learning a little Power Shell from the master, and maybe learn a little bit o' that DBA stuff this time around :)

See you in Boston!

Try-N-Save SSIS Packages

Mike C — Mon, 29 Nov 2010 05:17:00 GMT

The Try-N-Save SSIS Packages from my SSIS Dimensional Data Optimization presentation are available at http://cid-8f7e6c950afc6b3e.office.live.com/self.aspx/.Public/Presentation/TryNSave.zip.

I'm still working on getting the sample database uploaded - even compressed a backup is larger than SkyDrive's upload filesize limit. I'll script it out when I have time (in addition to DDL, there are some tables that need to be prepopulated).

High Performance Dimensional Data Loads With SSIS Presentation

Mike C — Sat, 20 Nov 2010 18:25:00 GMT

Just finished giving the SSIS High-Performance Dimensional Data Load presentation at SQLSaturday #59 NYC. Here are the slides in PDF format. I'll upload the Try-N-Save code and sample data later for attendees to play with.

Thanks to everyone who attended my session and thanks to Melissa D. and NJSQL for putting this together. For those who are interested in Alejandro Mesa's composable DML solution to the problem of Type 2 dimension updates, here's the complete statement from the demo:

INSERT INTO Dim.Geography_Hash
(
CityName, CountyFIPS, CountyName, StateFIPS,
StateUSPS, StateName, ZIP, TimeOffset,
DaylightSavingTime, StartDateID, CurrentFlag, BatchID,
LineageID, CubeInd, SortOrder, Hash
)
SELECT CityName, CountyFIPS, CountyName, StateFIPS,
StateUSPS, StateName, ZIP, TimeOffset,
DaylightSavingTime, StartDateID, CurrentFlag, BatchID,
LineageID, CubeInd, SortOrder, Hash
FROM
(
MERGE INTO Dim.Geography_Hash AS Target
USING Staging.Geography_Hash AS Source
ON Target.ZIP = Source.ZIP
  AND Target.CurrentFlag = Source.CurrentFlag
WHEN MATCHED AND Target.Hash <> Source.Hash
  THEN UPDATE SET CurrentFlag = 'N'
WHEN NOT MATCHED
  THEN INSERT
  (
   CityName, CountyFIPS, CountyName, StateFIPS,
   StateUSPS, StateName, ZIP, TimeOffset,
   DaylightSavingTime, StartDateID, CurrentFlag, BatchID,
   LineageID, CubeInd, SortOrder, Hash
  )
  VALUES
  (
   Source.CityName, Source.CountyFIPS, Source.CountyName, Source.StateFIPS,
   Source.StateUSPS, Source.StateName, Source.ZIP, Source.TimeOffset,
   Source.DaylightSavingTime, Source.StartDateID, Source.CurrentFlag, Source.BatchID,
   Source.LineageID, Source.CubeInd, Source.SortOrder, Source.Hash
  )
OUTPUT $action, inserted.CityName, inserted.CountyFIPS, inserted.CountyName, inserted.StateFIPS,
   inserted.StateUSPS, inserted.StateName, inserted.ZIP, inserted.TimeOffset,
   inserted.DaylightSavingTime, inserted.StartDateID, inserted.CurrentFlag, inserted.BatchID,
   inserted.LineageID, inserted.CubeInd, inserted.SortOrder, inserted.Hash
)
AS T
(
action, CityName, CountyFIPS, CountyName, StateFIPS,
StateUSPS, StateName, ZIP, TimeOffset,
DaylightSavingTime, StartDateID, CurrentFlag, BatchID,
LineageID, CubeInd, SortOrder, Hash
)
WHERE action = 'UPDATE';

As mentioned, the OUTPUT clause on the inner MERGE statement feeds the outer INSERT clause. Next stop is SQLSaturday #61 in DC at the beginning of December.

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.

SSIS: Mo' Secure Configurations

Mike C — Mon, 18 Jan 2010 22:19:00 GMT

I ran into an issue the other day and I needed a solution for automically configuring my SSIS packages from securely stored DBMS connection strings. Problem is that most DBMSs don’t support Integrated Authentication—they require a username and password. Storing the username/password combo in the connection string in plain text is a security risk, so the question becomes an issue of storing this information securely.

There are several ways to store configuration data for SSIS packages. Probably one of the easiest and most popular methods is to use .dtsConfig files stored in the file system. This method presents some challenges when you want to secure usernames and passwords in the files. I won’t go into the details, since this post isn’t about .dtsConfig files, but Jamie Thompson does a great job of running down the .dtsConfig file options over at his old blog: http://consultingblogs.emc.com/jamiethomson/archive/2007/04/26/SSIS_3A00_-Storing-passwords.aspx.

After abandoning .dtsConfig files the idea of combining SSIS SQL Server configurations and cell-level encryption popped into my head. I created a quick proof of concept and tested it. Then SSIS guru Jamie Thompson pointed me over to a blog post from Larry Charlton (from way back in 2007!) at http://curionorg.blogspot.com/2007/05/encrypted-sql-server-ssis.html that is very similar to what I came up with. Kudos to Larry for a nice piece of code. My concept is very similar to Larry’s, but with one major difference in implementation that I’ll cover below.

So the idea is that SSIS, by default, looks for a table named [dbo].[SSIS Configurations] to get its configuration data. This script builds the table:

CREATE TABLE [dbo].[SSIS Configurations]

(

ConfigurationFilter nvarchar(255) NOT NULL,

ConfiguredValue nvarchar(255) NULL,

PackagePath nvarchar(255) NOT NULL,

ConfiguredValueType nvarchar(20) NOT NULL

);

Here’s a breakdown of the columns that SSIS is looking for:

ConfigurationFilter holds a name you assign to the configured value. In my sample package I named my entry “Password Connection String”.
ConfiguredValue is the actual value. In the sample package this is the actual connection string with username and password.
PackagePath is the SSIS package property path. In the example the connection string value is used to configure the connection string of an OLEDB Connection named “Password Login Connection”. The path is “\Package.Connections[Password Login Connection].Properties[ConnectionString]”.
ConfiguredValueType tells SSIS the data type of your ConfiguredValue. In this case the data type is “String”.

Once you create the [dbo].[SSIS Configurations] table you can populate it with a connection string either through the SSIS designer or using a SQL INSERT statement like the following:

INSERT INTO [dbo].[SSIS Configurations]

(

ConfigurationFilter,

ConfiguredValue,

PackagePath,

ConfiguredValueType

)

VALUES

(

'Password Connection String',

'Data Source=(local);User ID=TestLogin;Password=p@$$w0rd;Initial Catalog=Test;',

'\Package.Connections[Password Login Connection].Properties[ConnectionString]',

'String'

);

Note: if you use the SSIS designer in BIDS to populate the table with connection strings, it will strip out sensitive information like passwords.

You can use SQL Server security to restrict access to this table, but for my purposes that wasn’t good enough. Due to IT policies I specifically had to obscure the plain text of the connection string since it contained a password. For that I decided to turn to SQL Server cell-level encryption. The trick is that SSIS needs to be able to read the data directly from the table as plain text, but it shouldn’t be stored as plain text.

Larry’s implementation uses the T-SQL EncryptByPassPhrase and DecryptByPassPhrase functions, both of which require you to store a plain text passphrase somewhere to encrypt and decrypt your data and limit you to Triple-DES encryption. But the main problem I have with this method is that storing a plain text passphrase somewhere doesn’t solve the security issue—it just passes the buck. The same problem is evident when you try to encrypt SSIS packages by password.

Make no mistake, encryption key management is the hardest part of encryption—it’s a simple case of Turtles All The Way Down. Personally I don’t want to take on the responsibility for managing plain text passwords and passphrases—I’d rather let the server do that for me. So my solution needs to eliminate the need to store and pass plaintext passwords and passphrases to encrypt and decrypt the secure data.

To secure data using cell-level encryption first we need to do a little setup. The first step is to create a database master key (DMK), as shown below.

Note: always backup new encryption keys and certificates as soon as you create them!

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Ma5t3rK3yP@55';

Then we need to create a certificate that is secured by the database master key (DMK). For simplicity I named the certificate ConfigCert.

CREATE CERTIFICATE ConfigCert

AUTHORIZATION TestUser

ENCRYPTION BY PASSWORD = 'myT3stP@$$'

WITH SUBJECT = 'Configuration Encryption Certificate',

EXPIRY_DATE = '20201231';

ALTER CERTIFICATE ConfigCert

WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'myT3stP@$$');

Next we create a symmetric encryption key that’s protected by the certificate. I called the symmetric key ConfigSymKey.

CREATE SYMMETRIC KEY ConfigSymKey

AUTHORIZATION TestUser

WITH ALGORITHM = AES_256,

KEY_SOURCE = 'I am the very model of the modern major general',

IDENTITY_VALUE = 'Stella! Stella!'

ENCRYPTION BY CERTIFICATE ConfigCert;

Note: it’s a best practice to always specify a KEY_SOURCE and IDENTITY_VALUE in the CREATE SYMMETRIC KEY statement so you can recreate the same symmetric key when necessary. There is no BACKUP SYMMETRIC KEY statement.

Then we need to create a slight variant of the [dbo].[SSIS Configurations] table designed to store the encrypted configuration values, as shown below.

CREATE TABLE [dbo].[SSIS_Configurations_Encrypted]

(

ConfigurationFilter nvarchar(255) NOT NULL,

ConfiguredValue varbinary(512) NULL, -- This column changed

PackagePath nvarchar(255) NOT NULL,

ConfiguredValueType nvarchar(20) NOT NULL

);

The only difference between this table and the [dbo].[SSIS Configurations] table is the ConfiguredValue column. In the encrypted version of the table this column is varbinary instead of varchar, and it’s bigger than it’s unencrypted version. The reasons are that (1) all SQL Server encryption functions return varbinary data, and (2) the encrypted data will always be longer than the plain text.

The next step is to drop the [dbo].[SSIS Configurations] table and create a view with the same name.

DROP TABLE [dbo].[SSIS Configurations];

CREATE VIEW [dbo].[SSIS Configurations]

SELECT ConfigurationFilter,

CAST

(

DecryptByKeyAutoCert

(

Cert_ID(N'ConfigCert'), NULL, ConfiguredValue

) AS nvarchar(255)

) AS ConfiguredValue,

PackagePath,

ConfiguredValueType

FROM [dbo].[SSIS_Configurations_Encrypted];

The view uses the DecryptByKeyAutoCert function to automatically decrypt the ConfiguredValue column. This works because SSIS does not differentiate between the view and the table of the same name. This is a good thing, because it simplifies our process quite a bit. Our next step is to make sure that inserts and updates to the view properly update the underlying table with encrypted data. For this we create an INSTEAD OF INSERT, UPDATE trigger.

CREATE TRIGGER SSIS_Configurations_Trigger

ON [dbo].[SSIS Configurations]

INSTEAD OF INSERT, UPDATE

BEGIN

SET NOCOUNT ON;

-- SQL treats updates as a logical delete followed by an insert

-- So look at the deleted virtual table to see if anything was deleted

DECLARE @IsUpdate char(1) = CASE WHEN (SELECT COUNT(*) FROM deleted) = 0 THEN 'N'

ELSE 'Y'

END;

-- Open the symmetric key

OPEN SYMMETRIC KEY ConfigSymKey

DECRYPTION BY CERTIFICATE ConfigCert;

-- Do a merge

MERGE [dbo].[SSIS_Configurations_Encrypted] AS target

USING inserted AS source

(

target.ConfigurationFilter = source.ConfigurationFilter

AND target.PackagePath = source.PackagePath

AND target.ConfiguredValueType = source.ConfiguredValueType

)

WHEN MATCHED AND @IsUpdate = 'Y' THEN

UPDATE SET target.ConfiguredValue = EncryptByKey(Key_Guid(N'ConfigSymKey'), source.ConfiguredValue)

WHEN NOT MATCHED AND @IsUpdate = 'N' THEN

INSERT

(

ConfigurationFilter,

ConfiguredValue,

PackagePath,

ConfiguredValueType

)

VALUES

(

source.ConfigurationFilter,

EncryptByKey(Key_Guid(N'ConfigSymKey'), source.ConfiguredValue),

source.PackagePath,

source.ConfiguredValueType

);

-- Close the symmetric key

CLOSE SYMMETRIC KEY ConfigSymKey;

END;

This trigger intercepts INSERT and UPDATE requests to the [dbo].[SSIS Configurations] view to automatically encrypt the ConfiguredValue column, which is then used to update the encrypted data in the underlying [dbo].[SSIS_Configurations_Encrypted] table. We can re-run the previous INSERT statement from the beginning of the article to test it.

INSERT INTO [dbo].[SSIS Configurations]

(

ConfigurationFilter,

ConfiguredValue,

PackagePath,

ConfiguredValueType

)

VALUES

(

'Password Connection String',

'Data Source=(local);User ID=TestLogin;Password=p@$$w0rd;Initial Catalog=Test;',

'\Package.Connections[Password Login Connection].Properties[ConnectionString]',

'String'

);

A couple of quick SELECT queries reveal the results:

SELECT *

FROM dbo.[SSIS_Configurations_Encrypted];

SELECT *

FROM dbo.[SSIS Configurations];

The results from the first query show the ConfiguredValue is stored unreadable/encrypted. Querying the view returns the unencrypted plain text.

Making a clear fashion statement with the classic belt and suspenders, you can deny permissions to the underlying table and the view to those that don’t need to see it, and you can deny access to the symmetric key and certificate to those same folks. If someone gains access to the view or the table, they still need access to the encryption key and certificate to decrypt the contents. Without this access, anyone querying the view will see NULL in the ConfiguredValue column.

Sample scripts and SSIS package are attached to this entry.

UPDATE: Andy L. pointed out that [dbo].[SSIS Configurations] is just the default name SSIS uses for your configuration entries table. You can actually change it in the designer at design time, or in SQL Server at build time. The designer lets you choose which table to point at. The important thing is that the columns need to have the correct names.

"SQL Server MVP Deep Dives" Book Available for Early Access/Pre-Orders

Mike C — Thu, 17 Sep 2009 00:21:00 GMT

The MVP authored book, SQL Server MVP Deep Dives is now available for early access and pre-orders at http://manning.com/nielsen/. The book is divided into 5 sections that cover everything from building a proper relational database to developing BI solutions:

1. Database Design and Architecture
2. Database Development
3. Database Administration
4. Performance Tuning and Optimization
5. BI Development

This book covers a lot of ground with a lot of expert tour guides. Weighing in at 850 pages, it was written, tech-reviewed and edited by dozens of Microsoft MVPs with intimate knowledge of SQL Server. The contributor list includes some of the biggest names in SQL Server: Paul Nielsen, Adam Machanic, Kalen Delaney, Itzik Ben-Gan, Aaron Bertrand, Hugo Kornelis, John Paul Cook, and several others. For me getting a copy of this book will be like getting the opportunity to pick the brains of some of the most knowledgable folks in the industry on a wide variety of topics.

This is already one of my favorite books that I've had the chance to contribute to (I wrote a couple of chapters discussing on SQL Server XML in general and SQL Server XQuery specifically) because all of the royalties go to a charity that helps children who are war victims!

So go get the book, learn what the pros know, and do your good deed for the day -- all at the same time!

You can get more info on SQL Server MVP Deep Dives at Manning Publications: http://manning.com/nielsen/ and at Amazon http://www.amazon.com/SQL-Server-MVP-Deep-Dives/dp/1935182048/.

DTS still alive in 2008?

RickHeiges — Wed, 22 Aug 2007 09:55:00 GMT

I was on a call with several other folks the other day discussing "Katmai". The topic of DTS came up. MSFT had indicated that support for DTS would not go beyond SQL Server 2005 in a variety of channels. I can remember the dozens of Upgrade Advisor messages that always appeared. I just revisited the "Advisory" text. It says that support for DTS will be discontinued at a future date.

Well, it looks like a bunch of folks out there still love those DTS packages. Why do I say this? In the July CTP of SQL Server 2008 BOL, there is a section on DTS. Basically, there is nothing different about DTS support for 2008.

No News is Good News? Yes and No. Yes - that means that many organizationis will not be forced to rewrite DTS packages into SSIS as they move forward. No - that means that many organizations will not take advantage of newer technology.

Also, no SSIS in Express edition in 2008. BOL still recommends using 2000 DTS components here.