THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Michael Coles: Sergeant SQL

SQL Server development, news and information from the front lines

  • T-SQL Tuesday #004: Why Doesn't TDE Encrypt My FILESTREAM Data?

    This post is my entry for Adam Machanic's T-SQL Tuesday #004, hosted this time by Mike Walsh. I was at the RSA Conference in San Francisco last week discussing database encryption options in SQL Server 2008 and one question seemed to keep coming up. The question concerns FILESTREAM and Transparent Data Encryption (TDE), but first a little background:

    FILESTREAM is a new SQL Server 2008 feature. When you apply the FILESTREAM attribute to a varbinary(max) column SQL Server stores your BLOB data in a "FILESTREAM data container" (an NTFS directory structure) instead of directly in the database (the MDF and NDF files that normally hold all your data). The advantages of FILESTREAM are speed (streaming NTFS access for large files) and the ability to store BLOB data larger than 2.1 GB.

    One of the downsides of FILESTREAM concerns another new feature, TDE. TDE transparently encrypts your database, adding a layer of protection against physical theft of your database files and storage devices. But TDE does not encrypt FILESTREAM data. The question I kept getting was "why?"  The answer is fairly simple -- but a picture's worth a thousand words:

    TDE

    As you can see in the picture, TDE sits midway between your physical storage and SQL Server's IO buffers. This ensures that everything that passes through the IO buffers gets encrypted on its way to persistent storage and decrypted on its way back out of storage. This is also why it's "transparent" to your applications, developers and users. SQL Server reads and writes data through the IO buffers in 8 KB pages. FILESTREAM achieves much of its performance enhancement for BLOB data by simply bypassing the IO buffers. But since TDE acts only on data passing through the IO buffers, it misses FILESTREAM BLOB data completely.

    Fortunately there are plenty of other options for encrypting your FILESTREAM data -- you can use Windows Encrypting File System (EFS), BitLocker or third-party file/folder/volume encrypting software, for instance.

  • Calculating Holidays in SQL

    Ask about nearly any kind of SQL-based date calculation in the newsgroups, and you'll likely get responses that include use of an auxiliary calendar table.  It's a really good idea, and something I highly recommend to anyone who has to do date-based calculations on the server.  For those who do dimensional modeling, you'll probably notice the auxiliary calendar table closely resembles a Time dimension with one-day intervals.  I won't get into too much detail on it, since ASPFAQ and the newsgroups cover it well.

    What I do want to talk about is calculating holidays, which can be important when populating your auxiliary calendar table.  When you create an auxiliary calendar table you may need to perform calculations based on business days, for instance; or you may need to schedule activities before, after, or even during certain holidays.  There are two types of holidays that I'll address:

    • Fixed holidays that fall on the same day every year
    • Floating holidays for which the date can change from year to year

    Fixed holidays include holidays that always fall on the same day each year.  Some examples of fixed holidays include Christmas (always December 25 in the West), Canada Day (always July 1), U.S. Independence Day (always July 4).  These are fairly easy to detect and set in your auxiliary calendar table.  Just look for the static month + day combinations in your table and update it accordingly.

    Floating holidays are far more interesting and difficult to calculate.  Take Easter.  Easter actually has two definitions.  The popular definition is: 

    • "Easter Day is the first Sunday after the full moon that occurs next after the vernal equinox"

    This is not the exact ecclesiastical definition though.  The full moon here is not the astronomical full moon but an "ecclesiastical moon" that's determined through precalculated lookup tables.  The ecclesiastical rules are:

    • Easter falls on the first Sunday following the first ecclesiastical full moon that occurs on or after the day of the vernal equinox
    • This particular ecclesiastical full moon is the 14th day of a tabular lunation (new moon)
    • The vernal equinox is fixed as March 21

    I'm not going to recount the long and painful history leading to the modern definition of Easter--but the details can be found on Wikipedia if you're interested.  The main point is that Easter calculation is fairly complex.  The udf_CalculateEaster function below accepts the year (integer) as a parameter and returns the date of Easter for that year (datetime).  Notice the large number of calculations required to make this function work:

    CREATE FUNCTION dbo.udf_CalculateEaster
    (
      @Year INT
    )
    RETURNS DATETIME
    AS
    BEGIN
        DECLARE @Date DATETIME,
        @c INT,
        @n INT,
        @i INT,
        @k INT,
        @j INT,
        @l INT,
        @m INT,
        @d INT;

        SELECT @n = @Year - 19 * (@Year / 19),
        @c = @Year / 100,
        @k = (@c - 17) / 25,
        @i = @c - @c / 4 - (@c - @k) / 3 + 19 * @n + 15,
        @i = @i - 30 * (@i / 30),
        @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11)),
        @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4,
        @j = @j - 7 * (@j / 7),
        @l = @i - @j,
        @m = 3 + (@l + 40) / 44,
        @d = @l + 28 - 31 * (@m / 4),
        @Date = CAST
        (
          CAST(@Year AS CHAR(4)) +
          RIGHT
          (
            '0' + CAST
            (
              @m AS VARCHAR(2)
            ), 2
          ) +
          RIGHT
          (
            '0' +CAST
            (
              @d AS VARCHAR(2)
            ), 2
          ) AS DATETIME
        );
        RETURN @Date;
    END;
    GO

    If you want the date for Easter 2010, just run a query like the following:

    SELECT dbo.udf_CalculateEaster (2010);
    GO

    The result is 2010-04-04.

    Another somewhat daunting type of floating holiday is the type that is based on the Nth weekday of the month. These holidays are defined using terms like "the 4th Thursday of November" (U.S. Thanksgiving), "the 3rd Monday in January" (U.S. Martin Luther King Day), or even "the *last* Monday in May" (U.S. Memorial Day). These floating holidays are not as hard to calculate as Easter, but still a little tricky (particularly Memorial Day).

    To calculate these dates we need a function like udf_nthWeekDay below. This function accepts 4 parameters:

    • @n = integer that represents which instance of the weekday you're looking for.  This would be the 4 in "4th Thursday in November".
    • @weekDay = 3-character day of week ("SUN" = Sunday, "MON" = Monday, etc.)  I used character abbreviations instead of numbers just to make it a little easier.
    • @year = integer year for which to calculate
    • @month = integer month for which to calculate (1 = January, 2 = February, etc.)

    As you can see below, the Nth weekday calculation is pretty simple.

    CREATE FUNCTION dbo.udf_nthWeekDay
    (
      @n       INT,
      @weekDay CHAR(3),
      @year    INT,
      @month   INT
    )
    RETURNS DATETIME
    AS
    BEGIN
      DECLARE @date    DATETIME,
        @dow         INT,
        @offset      INT,
        @wd          INT;
       
      SELECT @wd = CASE @weekDay
          WHEN 'SUN' THEN 1
          WHEN 'MON' THEN 2
          WHEN 'TUE' THEN 3
          WHEN 'WED' THEN 4
          WHEN 'THU' THEN 5
          WHEN 'FRI' THEN 6
          WHEN 'SAT' THEN 7
        END,
        @date = CAST
        (
          CAST(@year AS VARCHAR(4)) +
          RIGHT
          (
            '0' + CAST
            (
              @month AS VARCHAR(2)
            ), 2
          ) +
          '01' AS DATETIME
        ),
        @dow = DATEPART(dw, @date),
        @offset = @wd - @dow,
        @date = DATEADD(day, @offset + (@n - CASE WHEN @offset >= 0 THEN 1 ELSE 0 END) * 7, @date);
      RETURN @date;
    END;
    GO

    To get the date for Thanksgiving 2024 you could call the function like this:

    SELECT dbo.udf_nthWeekDay
      (
        4,
        'THU',
        2024,
        11
      );

    The result is 2024-11-28.  Now for a holiday like U.S. Memorial Day ("the last Monday in May") the calculation is a little trickier.  You have no idea up front how many Mondays there are in any given May.  As an example, in 2010 and 2011 there are 5 Mondays in May; in 2012 and 2013 there are only 4.  You could try to figure out the number of Mondays in May of the given year, but that's way too much work.

    There's one thing we know about the last Monday in May with absolute certainty:  after the last Monday in May, the next Monday we encounter will *always* be the first Monday in June.  So the easier way to figure out the last Monday in May is to calculate the first Monday in June and then back into it (subtract 7 days), like this:

    SELECT DATEADD
    (
      day,
      -7,
      dbo.udf_nthWeekDay
      (
        1,
        'MON',
        2012,
        6
      )
    );

    By calculating the date of the first Monday in June 2012, we can then subtract 7 days to get the date of the last Monday of May: 2012-05-28.

    You can use the functions to calculate holidays for your auxiliary calendar table.  I covered U.S. federal (and some other national) holidays in this one, but it can be extended to cover other national, state, and local holidays.

  • Service Packs... Coming to a Download Near You!

    Based on feedback from the community (https://connect.microsoft.com/SQLServer/feedback/details/522123/sql-server-2008-service-pack-2 and https://connect.microsoft.com/SQLServer/feedback/details/522122/service-pack-4-for-sql-server-2005), Microsoft has announced plans to release Service Pack 2 for SQL Server 2008 and Service Pack 4 for SQL Server 2005. The official announcement from the SQL Server Group Project Manager Matthias Berndt is here.

    Here's the abbreviated version:

    • Service Pack 2 for SQL Server 2008 is targeted for a Q3 release.
    • Service Pack 4 for SQL Server 2005 is targeted for a Q4 release.

    SP 4 is currently scheduled to be the final SP for 2005. The announcement also contains some info. about SQL 2005 going into extended support from April 2011 to April 2016.

  • Almost T-SQL Tuesday #003: Calculating Products - An Oldie But Goodie

    Looks like I just missed the deadline for T-SQL Tuesday #003 [oh well, whatcha gonna do].  The other day someone asked me about an oldie but goodie that hits on a math-based relationship. SQL Server has several aggregate functions like SUM for summation. Summation is the addition of a set of numbers, and it's represented with sigma notation, like this:

    Sigma notation from Wikipedia

    The question was how do you calculate a product in SQL?  Keep in mind that SQL has no built-in PRODUCT aggregate. A product is the multiplication of a series of numbers, and is represented with capital PI notation:

    Capital PI notation 

    The question was how do you perform a product aggregation in SQL? The most obvious way to solve the problem is with a simple cursor or loop. You could also create a SQL CLR UDF. But we can solve it even simpler than that, in pure SQL. So how do we solve it with a SQL set-based solution?

    Well, mathematics provides us a relationship between logarithms and exponents that allows us to turn a product into a simple summation. The code below demonstrates.

    CREATE TABLE Tab
    (
     
    val float
    );
    GO

    INSERT
    INTO Tab (val)
    VALUES (9.3),
     
    (4.5),
     
    (2.6),
     
    (11.4),
     
    (3);
    GO

    SELECT
    EXP(SUM(LOG(val))) AS product
    FROM Tab;
    GO

    The sample sums the natural logarithms of val, and then returns the exponential of that sum. The end result is the product of the values in the val column. One thing to watch out for is that this method will error out if any of the values are <= 0. You can accomodate for this issue with a CASE expression, but that's for another day.

     WHO DAT?

  • NJ .NET User Group Presentation: Building a SQL Server Search Engine in .NET

    I'll be presenting at the Northern NJ .NET User Group meeting on Tuesday February 9.  Topics we'll cover include:

    • Intro to SQL Server 2008 Integrated Full-Text Search (iFTS) features and functionality, including:
      • Full-text indexes
      • Thesaurus
      • Word breakers, filters and stemming
      • Multilanguage support
    • Intro to SQL Server 2008 FILESTREAM storage
    • Troubleshooting with iFTS dynamic management views and functions
    • Building a simple and powerful .NET-based search engine-style interface

    Click here for more information: http://www.setfocus.com/n3ug/welcome.aspx

    See you there!

  • It's Official - SQLSaturday is Coming to NYC!

    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?

    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;

    GO

     

    -- Switch to the new test database

    USE Test;

    GO

     

    -- Create database master key

    CREATE MASTER KEY

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

    GO

     

    -- Create a test certificate

    CREATE CERTIFICATE TestCert

       WITH SUBJECT = 'Test Certificate',

       EXPIRY_DATE = '20151231';

    GO

     

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

    -- it's installed correctly

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

    GO

     

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

     

    USE Test;

    GO

     

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

    -- directory exists

    BACKUP CERTIFICATE TestCert

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

    GO

     

    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)

    AS

    BEGIN

          SELECT CAST

                (

                      DECRYPTBYCERT

                      (

                            CERT_ID('TestCert'),

                            @EncryptedPassword

                      ) AS nvarchar(100)

                ) AS DecryptedPassword;

    END;

    GO

     

    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.

     

     

  • Elevating Kentucky

    Kentucky has launched their Microsoft "Elevate America" website at https://kyelevateamerica.ky.gov/. Kentucky residents can register for free Microsoft training and certification at this link through the Commonwealth's Office of Employment and Training.  Their website indicates about 20,250 vouchers are currently available and they're going to go fast.  They also indicate that vouchers will go on a first-come, first-served basis and that "unemployed workers get first priority during the first two weeks of the program."

     

  • SSIS: Mo' Secure Configurations

    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

    );

    GO

     

    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'

    );

    GO

     

    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';

    GO

     

     

    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';

    GO

     

    ALTER CERTIFICATE ConfigCert

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

    GO

     


    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;

    GO

     

    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

    );

    GO

     

    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];

    GO

     

    CREATE VIEW [dbo].[SSIS Configurations]

    AS

    SELECT ConfigurationFilter,

          CAST

          (

             DecryptByKeyAutoCert

             (

                Cert_ID(N'ConfigCert'), NULL, ConfiguredValue

             ) AS nvarchar(255)

          ) AS ConfiguredValue,

          PackagePath,

          ConfiguredValueType

    FROM [dbo].[SSIS_Configurations_Encrypted];

    GO
     

    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

    AS

    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

        ON

        (

                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;

    GO

     

    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'

    );

    GO

     

    A couple of quick SELECT queries reveal the results:

     

    SELECT *

    FROM dbo.[SSIS_Configurations_Encrypted];

    GO

     

    SELECT *

    FROM dbo.[SSIS Configurations];

    GO

     

    SELECT query results

    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.

  • T-SQL Tuesday: Easy Extended Properties

    Ahhh, attention to detail. I misread Adam's T-SQL Tuesday rules and posted early. So here I go again :) This time the puzzle is how to add and update metadata via extended properties in the database with minimal headaches.

    SQL Server supports a great feature for storing database object metadata in the database in the form of extended properties. The main benefits of extended properties over custom solutions are: (1) extended properties are managed and stored internally by SQL Server; (2) many third-party applications retrieve/use extended properties. Perhaps the best known extended property is the "MS_Description" property, which is set by the "Description" field in the SSMS designer pages (shown below):

    Setting MS_Description property in SSMS 

    Extended properties are created as user-defined key/value pairs assigned to database objects. You can define the extended propery keys and their associated values to be anything you like, although there are a few commonly used ones (like "MS_Description"). The values are defined as sql_variant data types. Here are some sample key/value pairs:

     

    Key

    Value

    MS_Description

    This table represents the culmination of a lifelong dream.

    Caption

    GrandTotal Column

    Last_Update

    2009-07-09

    Version

    1.0.1

    Extended properties allow you to store metadata in the database closely tied to database objects. So what's the downside to extended properties? Basically the only real issue with them is the nonintuitive syntax. In order to use extended properties you have to use stored procedures like sp_addextendedproperty. The following stored procedure call sets the MS_Description extended property on the bar column of the dbo.foo table:

    EXECUTE sys.sp_addextendedproperty
      @name = N'MS_Description',
      @value = N'This is the bar column',
      @level0type = N'SCHEMA',
      @level0name = N'dbo',
      @level1type = N'TABLE',
      @level1name = N'foo',
      @level2type = N'COLUMN',
      @level2name = N'bar';

    As you can see in the example, this syntax requires you to specify not only the extended property name/value pair and the individual components of the column's fully-qualified name, but also the types of each object. Updating and deleting extended properties using the system stored procedures requires the same type of information. A painful process to say the least.

    The attached code sample creates an extended properties management schema named ExtProps. The ExtProps schema contains stored procedures that make it easier to add, update and delete extended properties. With these procedures you specify the object name using the standard SQL Server 1-, 2-, or 3-part naming convention along with the extended property name/value pair. The procedure will automatically figure out what type of object you're referencing. The following sample performs the same function as the previous example:

    EXECUTE ExtProps.PropInsert N'dbo.foo.bar',
      N'MS_Description',
      N'This is the bar column';

    The ExtProps.PropInsert, ExtProps.PropDelete and ExtProps.PropUpdate procedures use the object name you pass in to narrow down the type of object you're referencing. They then use the SQL Server catalog views to figure out exactly which object it should modify the extended properties on. The following table lists the types of object and the names expected by the procedures:

    Name

    Object Type

    NULL

    Current database

    1-part

    Schema

    2-part (schema.object)

    Table, View, Procedure, Function, Aggregate, Synonym, Queue

    3-part (schema.object.column)

    Column

    Note that these procedures do not yet handle all the different objects to which you can assign extended properties. For instance, it does not currently handle extended properties for procedure parameters, event notifications, and a handful of others.

    When you want to retrieve extended properties from the database you can use the standard system view, sys.extended_properties, or the fn_listextendedproperty system function. As an alternative, the ExtProps schema includes a new view -- ExtProps.Properties. This view has two advantages over the built-in system views:

    1. In addition to all the data returned by the sys.extended_properties view the ExtProps.Properties view also includes schema, object, and column names.
    2. The ExtProps.Properties view has triggers built on it that allow you to insert, delete, and update extended properties using standard SQL DML statements. The triggers actually call the relevant stored procedures, once for each row of extended property data being modified.

    The figure below show sample output from the ExtProps.Properties view:

    ExtProps.Properties View

    The script attached to this posting creates the ExtProps schema, procedures and view.

  • Parent-Child Build Scripts with SQLCMD

    On the SQL Server public programming newsgroup someone recently posted a question about an SSMS error ("Cannot parse script. 'System.OutOfMemoryException' thrown.") I hadn’t encountered this error myself, but the workaround is to break up very large scripts (50+ MB) into smaller scripts. Adam Machanic posted a T-SQL Tuesday challenge to post a solution to a puzzling situation, so this actually gives me a good opportunity to share how I structure my own build scripts -- which avoids this issue entirely.

    When I create database build scripts, I use the SQLCMD utility to run them from the command line instead of using SSMS or another tool. SQLCMD has its own commands, which it parses separately from SQL/T-SQL statements. These commands are not understood by SQL Server or other scripting tools like SSMS (exception: you can run SSMS in SQLCMD mode, but that’s another story). These special SQLCMD commands all start with a ":" at the front of the line.

    The SQLCMD command that makes parent-child structured build-scripts possible is the ":r" or "run" command, which tells SQLCMD to run another script file from within the current script file. In the figure below I’ve set up a local directory structure with database object creation scripts in subdirectories:

    The \Scripts directory contains a Create.All.Sql script. This script uses the SQLCMD run command to execute the Database\Create.Database.Sql script, the Create.All.Schemas.Sql script, and so on.  The Create.All.Schemas.Sql script calls the Person.Schema.Sql and Sales.Schema.Sql scripts in turn. The other Create.All.* scripts each call the object creation scripts in their subdirectories as well. Here’s what my Create.All.Sql script looks like:

    /*
        Create All Items
    */

    :r Database\Create.Database.sql
    :r Schemas\Create.All.Schemas.sql
    :r Types\Create.All.Types.sql
    :r Tables\Create.All.Tables.sql 

    Each :r command kicks off the next level of child packages in turn.

    SQLCMD has another great feature known as scripting variables that you can use to create dynamic scripts. Essentially you define a scripting variable on the command line with SQLCMD's -v command line option. Now the way scripting variables work, they are replaced wholesale in your scripts with their replacement value. So if you define a scripting variable named environment you can replace it with a value like "Dev", "QA" or "Prod" anywhere it occurs in your script. This is great for making dynamic scripts that need to be built across multiple environments.

    In the example I've used a scripting variable named database. You can set the value of the database variable from the command line with the -v option. In the example below I set the database variable to the value "Test".

    SQLCMD Sample Command Line

    The nice thing about the SQLCMD scripting variables is that once you declare them you can access them from the parent script you run (in this case Create.All.Sql) or from any child scripts that are run (like Create.Database.Sql, Create.All.Schemas.Sql, Person.Schema.Sql and Sales.Schema.Sql). Here’s the Create.Database.Sql script from the example:

    /*

     Create database

    */

    USE master;
    GO

    CREATE DATABASE $(database);
    GO 

    The scripting variable is accessed in the script with $(database). The scripting variable is replaced with its value by SQLCMD, so in the example SQL Server sees this:

    /*

     Create database

    */

    USE master;
    GO

    CREATE DATABASE Test;
    GO 

    One thing to keep in mind when you use this scripting pattern is that every script should end with the batch terminator (default is "GO").  If not you could end up with one script running into another and get some strange, not-very-helpful error messages.

    Another thing you need to know is that scripting variables are replaced wholesale with their replacement text. This makes them very flexible, since you can replace text anywhere in the script with anything you want. It can also be dangerous if your script is run by someone with malicious intent. A malicious user can replace a scripting variable with T-SQL statements that could damage your data or database structure; so keep your scripting variable-enabled scripts out of the hands of potentially malicious users.

    I've attached a sample ZIP file with the directory structure shown in the example above. The scripts build out a few database objects from the AdventureWorks sample database.

  • Y-2.01K Bug

    Who would have thought the lessons learned in 1999 weren't remembered as we move into the new year?  Here's a quick roundup of Y-2*K stories:

    • If you have a German credit card issued recently you may not be able to use it thanks to the Y-2.01K bug.
    • It looks like Symantec Endpoint Manager and Apache SpamAssassin also had problems moving into the new year.
    • I guess even Windows is not immune, as several Windows Mobile users suddenly started getting messages dated from 6 years in the future. Thanks to the Y2K16 bug Windows Mobile users are receiving messages from the year 2016 instead of 2010 (my money's on 0x10 = 16 decimal).
  • You're a mean one...

    Vince threw out a question - how did I create my geeky Christmas card at http://sqlblog.com/blogs/michael_coles/archive/2009/12/22/merry-christmas.aspx?  For those who haven't seen it yet -- this is your spoiler alert -- go check it out before you read on.

    The script in the blog draws a picture of the Grinch with the SQL 2008 Geometry data type.  Unfortunately the way I wanted to create it is a much better story than how I ended up making it.  Originally I wanted to take a Grinch image, write a little .NET app to read in the bitmap and convert all the image borders to vector graphics.  After I spent about 30 minutes working on the app to pick apart the raster image and find the edges I figured out it would be a heckuva lot easier to just fat-finger the coordinates in, especially for such a simple image.

    I ended up just drawing a simple vector image of the Grinch in PaintShop Pro.  Then I opened up Excel and moved the mouse over every corner in the image and hand-keyed the coordinates into a spreadsheet.  I used Excel to create some concatenated string SELECT statements and copied/pasted it all into SSMS.

    ...Mr. Grinch 

    I did find out pretty quickly, by the way (and yes, I should have known this), that the y-coordinates in the PaintShop Pro window are the opposite of the Geometry data type.  That is, (0, 0) is the upper left corner and the y coordinates increase as you go down in PaintShop.  So my original Grinch image actually printed upside down.  That's why you see all the negative numbers in the final script :)

    Anyway the story of how I made it is a lot less interesting than how I *wanted* to make it :)

  • Tracking Santa(?)

    OK, last Christmas post for this year.  So my daughter and her friend have been asking me all morning "where's Santa?" and "when's Santa going to be here?"  After stumbling around for an answer I Googled up the official NORAD Santa Tracker at http://www.noradsanta.org/en/index.html.  The NORAD Santa Tracking Service started in 1955, when Sears Roebuck & Co. printed a full-page ad telling kids to call Santa directly -- but they printed the wrong phone number.  In an ironic twist, the phone number in the ad turned out to be the direct emergency hotline to the NORAD commander.  The NORAD Director of Operations, Colonel Harry Shoup, put his people to the task of answering the incoming phone calls and giving kids status updates on Santa's progress.  Apart from the occasional little glitch (last year Toronto somehow became a major U.S. city, for instance), NORAD's Santa-Tracking service has been running relatively smoothly for over 50 years.

    Santa's Coming To Town!

    More recently NORAD teamed up with Google to bring your kids real-time Web-based Santa-Tracking capability.  When we last checked, Santa was somewhere over Nepal (see picture) and headed this way.  NORAD also keeps track of how many presents Santa delivers at each stop -- 718 million at last count (pretty impressive in this economy).  There are also some nice narrated video clips of some of Santa's major stops.  Finally there's also a toll-free number 1 (877) 446-6723 [1 (877) HI NORAD] your kids can call to speak with some of Santa's "helpers".

    Happy holidays!  Enjoy!

  • Merry Christmas

    The script below generates the geekiest Christmas card in the world.  To see it, follow these instructions:

    (1) Copy and paste the script below into SQL Server Management Studio 2008
    (2) Execute the script
    (3) Look at the "Spatial Results" tab in SSMS

    Merry Christmas!

    -- Begin copy here

    SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION(
     POLYGON(
      (169 -166, 118 -152, 93 -154, 45 -192, 39 -208, 35 -233, 37 -262, 45 -289, 42 -328, 44 -347, 53 -376, 60 -359,
       64 -337, 63 -314, 59 -301, 48 -289, 50 -269, 54 -246, 60 -230, 72 -216, 57 -217, 73 -213, 68 -196, 75 -192,
       72 -197, 75 -213, 77 -198, 88 -180, 80 -198, 77 -214, 85 -245, 103 -248, 99 -245, 100 -242, 103 -240, 112 -240,
       104 -238, 106 -227, 116 -214, 129 -209, 139 -213, 134 -210, 142 -204, 150 -204, 143 -201, 147 -196, 154 -194,
       164 -196, 156 -193, 158 -185, 163 -177, 173 -174, 166 -174, 171 -165, 169 -166)
      ),
     POLYGON(
      (281 -177, 289 -176, 296 -179, 302 -182, 307 -187, 283 -426, 275 -494, 275 -534, 122 -534, 127 -509, 134 -508,
       135 -518, 143 -525, 155 -523, 158 -517, 166 -520, 176 -517, 181 -511, 190 -510, 196 -502, 196 -494, 202 -491,
       204 -483, 204 -480, 211 -474, 213 -462, 219 -455, 221 -449, 217 -439, 219 -431, 219 -422, 214 -413, 238 -382,
       243 -364, 243 -342, 249 -321, 257 -289, 271 -214, 281 -177)
     ),
     POLYGON(
      (122 -534, 127 -509, 120 -504, 116 -500, 116 -486, 112 -486, 106 -509, 104 -534, 122 -534)
     )
    )', 0)

    UNION ALL
     
    SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION(
     POLYGON(
      (190 -207, 201 -214, 209 -226, 212 -235, 214 -278, 219 -294, 234 -317, 241 -342, 242 -355, 241 -366, 239 -380,
       234 -388, 224 -400, 212 -413, 194 -435, 187 -446, 180 -459, 172 -467, 162 -471, 153 -470, 144 -464, 136 -453,
       125 -439, 117 -426, 102 -412, 88 -400, 73 -384, 70 -371, 68 -356, 71 -342, 76 -331, 82 -321, 90 -309, 92 -293,
       93 -282, 103 -280, 131 -256, 167 -217, 186 -204, 190 -207)
      ),
      POLYGON(
       (319 -40, 321 -57, 324 -79, 318 -84, 312 -84, 312 -91, 306 -104, 297 -105, 288 -110, 288 -90, 281 -78, 278 -59,
        295 -45, 309 -36, 314 -36, 319 -40)
      ),
      LINESTRING(
       309 -96, 304 -98,300 -98
      ),
      LINESTRING(
       311 -84, 304 -72, 303 -64, 304 -54
      ),
      LINESTRING(
       302 -68, 296 -66, 294 -59
      ),
      POLYGON(
       (319 -40, 328 -37, 344 -37, 349 -40, 348 -52, 344 -58, 332 -59, 321 -58, 319 -40)
      ),
      POLYGON(
       (322 -59, 343 -59, 346 -66, 344 -75, 338 -84, 331 -83, 323 -80, 322 -59)
      ),
      POLYGON(
       (323 -81, 327 -83, 338 -85, 337 -96, 332 -101, 325 -104, 317 -103, 311 -97, 312 -84, 318 -84, 323 -81)
      ),
      POLYGON(
       (306 -120, 307 -129, 311 -134, 312 -140, 309 -152, 308 -168, 296 -161, 286 -161, 284 -156, 288 -130,
        294 -128, 306 -120)
      ),
      POLYGON(
       (154 -472, 156 -476, 158 -485, 156 -491, 153 -498, 159 -487, 176 -474, 183 -464, 185 -453, 179 -460, 171 -467,
        165 -470, 155 -471, 154 -472)
      )
    )', 0)

    UNION ALL
     
    SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION(
     POLYGON(
      (171 -165, 179 -159, 185 -160, 182 -157, 190 -147, 202 -144, 211 -148, 214 -157, 206 -168, 215 -160, 221 -168,
       221 -175, 206 -184, 221 -178, 229 -186, 228 -192, 221 -197, 228 -197, 231 -205, 225 -212, 218 -211, 222 -216,
       219 -226, 211 -231, 204 -218, 191 -208, 201 -205, 191 -205, 178 -209, 167 -218, 144 -242, 128 -260, 111 -274,
       98 -282, 85 -283, 95 -284, 91 -299, 81 -299, 74 -294, 67 -294, 60 -290, 59 -284, 61 -279, 54 -268, 58 -258,
       66 -253, 74 -253, 77 -257, 77 -252, 85 -245, 103 -248, 99 -245, 100 -242, 103 -240, 112 -240, 104 -238, 106 -227,
       116 -214, 129 -209, 139 -213, 134 -210, 142 -204, 150 -204, 143 -201, 147 -196, 154 -194, 164 -196, 156 -193,
       158 -185, 163 -177, 173 -174, 166 -174, 171 -165)
     ),
     POLYGON(
      (122 -435, 114 -439, 108 -445, 106 -456, 111 -460, 118 -455, 112 -465, 109 -473, 109 -482, 115 -486, 122 -479,
       116 -489, 116 -497, 119 -503, 125 -507, 132 -508, 136 -504, 134 -515, 140 -522, 152 -524, 158 -517, 158 -510,
       160 -517, 170 -519, 178 -514, 181 -507, 184 -510, 193 -509, 196 -501, 195 -493, 200 -493, 204 -486, 200 -475,
       205 -478, 210 -472, 213 -466, 212 -461, 209 -457, 214 -459, 218 -457, 220 -450, 215 -440, 218 -434, 218 -423,
       214 -417, 208 -417, 196 -434, 185 -453, 183 -465, 176 -474, 160 -486, 156 -491, 153 -498, 155 -490, 158 -484,
       155 -476, 153 -472, 146 -466, 139 -457, 122 -435)
     ),
     POLYGON(
      (271 -152, 280 -153, 285 -158, 286 -161, 289 -160, 296 -160, 304 -165, 310 -172, 319 -172, 325 -176, 327 -180,
       326 -187, 320 -192, 319 -198, 315 -202, 307 -203, 307 -188, 300 -182, 290 -178, 282 -176, 278 -185, 266 -185,
       263 -181, 264 -177, 269 -175, 262 -176, 258 -171, 259 -166, 262 -164, 269 -165, 264 -162, 265 -157, 271 -152)
     )
    )', 0)

    UNION ALL

    SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION(
     POLYGON(
      (316 0, 329 0, 324 -39, 318 -40, 314 -36, 316 0)
     ),
     POLYGON(
      (303 0, 316 0, 315 -36, 304 -40, 303 0)
     ),
     POLYGON(
      (307 -104, 311 -98, 321 -105, 332 -105, 340 -108, 346 -117, 345 -124, 347 -129, 344 -131, 343 -126, 338 -125, 330 -122,
       324 -117, 328 -122, 326 -128, 323 -132, 318 -134, 317 -139, 312 -133, 307 -130, 306 -124, 308 -119, 311 -112, 306 -119,
       301 -124, 293 -128, 288 -128, 288 -132, 284 -127, 283 -122, 284 -114, 291 -108, 301 -104, 307 -104)
     )
    )', 0)

    UNION ALL

    SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION(
     POLYGON(
      (151 -306, 146 -311, 135 -316, 125 -318, 116 -317, 110 -312, 106 -306, 119 -303, 136 -300, 151 -306)
     ),
     POLYGON(
      (137 -303, 144 -304, 146 -307, 144 -310, 138 -312, 133 -309, 132 -305, 137 -303)
     ),
     POLYGON(
      (161 -302, 165 -296, 178 -288, 188 -286, 203 -289, 205 -294, 204 -305, 202 -307, 182 -312, 171 -310, 164 -306,
       161 -302)
     ),
     POLYGON(
      (184 -291, 185 -296, 184 -301, 176 -306, 173 -304, 170 -300, 172 -293, 179 -290, 184 -291)
     )
    )', 0)

    UNION ALL

    SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION(
     POLYGON(
      (143 -439, 142 -434, 148 -425, 155 -420, 162 -418, 168 -418, 180 -427, 183 -432, 183 -435, 181 -436, 176 -430,
       168 -427, 159 -424, 149 -426, 143 -433, 143 -439)
     ),
     LINESTRING(
      161 -424, 160 -419
     ),
     LINESTRING(
      173 -422, 176 -430
     )
    )', 0)

    UNION ALL

    SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION(
     LINESTRING(
      151 -326, 147 -331, 147 -335, 157 -333, 160 -336, 164 -338, 168 -335, 171 -334, 180 -335, 179 -330, 174 -328
     ),
     LINESTRING(
      146 -332, 136 -344, 132 -357, 130 -373, 132 -396, 136 -410, 137 -419
     ),
     LINESTRING(
      168 -338, 170 -344, 171 -351, 167 -391
     ),
     LINESTRING(
      181 -333, 187 -342, 188 -351, 192 -375, 191 -401, 190 -417, 190 -440, 186 -443, 182 -445, 179 -443, 175 -438
     ),
     LINESTRING(
      138 -436, 140 -442, 143 -446, 153 -436, 157 -431, 160 -432
     ),
     LINESTRING(
      150 -413, 153 -410, 157 -407, 164 -404, 173 -406, 176 -410
     ),
     LINESTRING(
      139 -446, 148 -457, 152 -460, 157 -462, 162 -463, 168 -462, 175 -457, 180 -447
     ),
     LINESTRING(
      126 -352, 121 -357, 117 -368, 116 -376, 117 -390, 121 -402, 135 -437
     ),
     LINESTRING(
      105 -396, 113 -412, 118 -425
     ),
     LINESTRING(
      216 -388, 214 -403, 208 -416
     ),
     LINESTRING(
      198 -360, 202 -370, 202 -382, 200 -403, 194 -434
     ),
     LINESTRING(
      175 -316, 181 -317, 193 -316, 205 -313
     ),
     LINESTRING(
      177 -318, 189 -323, 199 -323, 214 -316
     ),
     LINESTRING(
      174 -320, 186 -330, 199 -334, 213 -330, 220 -321
     ),
     LINESTRING(
      157 -326, 162 -322, 171 -323
     ),
     LINESTRING(
      155 -320, 161 -317, 167 -318
     ),
     LINESTRING(
      154 -316, 160 -312
     ),
     LINESTRING(
      106 -318, 110 -321, 121 -326, 132 -323, 148 -313
     ),
     LINESTRING(
      99 -324, 108 -334, 119 -335, 137 -325, 150 -312
     ),
     LINESTRING(
      94 -335, 100 -344, 111 -345, 123 -339, 137 -330, 147 -321, 153 -311
     ),
     LINESTRING(
      105 -307, 88 -304
     ),
     LINESTRING(
      106 -309, 94 -313
     ),
     LINESTRING(
      207 -294, 221 -295
     ),
     LINESTRING(
      205 -291, 219 -289
     ),
     LINESTRING(
      206 -290, 205 -283, 202 -281
     ),
     LINESTRING(
      158 -298, 158 -284, 160 -278
     ),
     LINESTRING(
      158 -284, 154 -263
     ),
     LINESTRING(
      154 -258, 152 -244
     ),
     LINESTRING(
      151 -272, 148 -266, 144 -261, 138 -257
     ),
     LINESTRING(
      153 -291, 149 -282, 140 -271, 130 -265
     ),
     LINESTRING(
      158 -264, 161 -250, 165 -241, 170 -235, 175 -233
     ),
     LINESTRING(
      169 -266, 173 -254, 178 -244, 182 -237
     ),
     LINESTRING(
      196 -244, 204 -220
     ),
     LINESTRING(
      167 -292, 198 -242
     ),
     LINESTRING(
      168 -291, 199 -241
     ),
     LINESTRING(
      169 -290, 201 -240
     ),
     LINESTRING(
      170 -289, 202 -240
     ),
     LINESTRING(
      171 -289, 204 -242
     ),
     LINESTRING(
      172 -288, 205 -244
     ),
     LINESTRING(
      173 -288, 210 -250
     ),
     LINESTRING(
      175 -287, 212 -252
     ),
     LINESTRING(
      178 -286, 217 -254
     ),
     LINESTRING(
      152 -306, 119 -288, 101 -293
     ),
     LINESTRING(
      153 -306, 119 -287, 102 -291
     ),
     LINESTRING(
      153 -306, 119 -286, 102 -287
     ),
     LINESTRING(
      153 -306, 120 -284, 103 -286
     ),
     LINESTRING(
      154 -305, 121 -282, 104 -283
     ),
     LINESTRING(
      156 -304, 121 -280, 104 -283
     )
    )', 0);

    -- End copy here

     

This Blog

Syndication

News

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement