"Random IV"?

I just encrypted a bunch of rows in a table. I was a little surprised
that encrypting N'A' resulted in a 44-byte result. N'AAAA' resulted in
a 52-byte result. That indicates that 8-byte padding is being used.
So the actual encrypted text of N'A' is only 8 bytes in length. What
in the world are the other 36 bytes being used for? I noticed an
interesting 'prefix' for the encrypted data:
0x0074126478AFB34BA54BA6280E2335EB010000
003AE8D337EF2E5B55BABDE251B245C1FCCD
B21C770073B642
0x0074126478AFB34BA54BA6280E2335EB010000
0057252C84D4B6C9F3FFA40444BB7E5ADC50
9233F73548C7A8
0x0074126478AFB34BA54BA6280E2335EB010000
0077113F30B10CC2A8FD976628C98C06C5F7
0E47AEEF3BE86C
It appears the first 20 bytes of every encrypted string are exactly the
same, regardless of the row, column, or data being encrypted. Further
these 20 bytes appear to be generated when the OPEN ... KEY statement
is issued. In between OPEN ... KEY statements, these first 20
characters change, but for multiple encryptions after an OPEN ... KEY
they remain the same.
These first 20 - 36 bytes wouldn't happen to be the "Random IV" would
they? One would think that storing the IV with the data would be
potentially less secure than letting the user manage their own IV...
What do you think?The first 16 prefix bytes represent the key identifier (a GUID). They are
used to determine what key should be used for decryption. They're
particularly useful if you use multiple encryption keys per column. The
decryption builtins will automatically search for the proper key and use it
to decrypt the blob.
So, if you're doing something like
select decryptbykey(SSN) from customers
it will decrpyt all SSNs encrypted by keys that you currently have opened.
The IV is indeed prefixed to the data. IV's are not meant to be secret,
they're just used to randomize the encryption output, so that patterns in
the cleartext data are not disclosed.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
<coles.michael@.gmail.com> wrote in message
news:1151728397.102411.65150@.h44g2000cwa.googlegroups.com...
>I just encrypted a bunch of rows in a table. I was a little surprised
> that encrypting N'A' resulted in a 44-byte result. N'AAAA' resulted in
> a 52-byte result. That indicates that 8-byte padding is being used.
> So the actual encrypted text of N'A' is only 8 bytes in length. What
> in the world are the other 36 bytes being used for? I noticed an
> interesting 'prefix' for the encrypted data:
> 0x0074126478AFB34BA54BA6280E2335EB010000
003AE8D337EF2E5B55BABDE251B245C1FC
CDB21C770073B642
> 0x0074126478AFB34BA54BA6280E2335EB010000
0057252C84D4B6C9F3FFA40444BB7E5ADC
509233F73548C7A8
> 0x0074126478AFB34BA54BA6280E2335EB010000
0077113F30B10CC2A8FD976628C98C06C5
F70E47AEEF3BE86C
> It appears the first 20 bytes of every encrypted string are exactly the
> same, regardless of the row, column, or data being encrypted. Further
> these 20 bytes appear to be generated when the OPEN ... KEY statement
> is issued. In between OPEN ... KEY statements, these first 20
> characters change, but for multiple encryptions after an OPEN ... KEY
> they remain the same.
> These first 20 - 36 bytes wouldn't happen to be the "Random IV" would
> they? One would think that storing the IV with the data would be
> potentially less secure than letting the user manage their own IV...
> What do you think?
>|||The first 20 bytes are not part of the ciphertext, it is really some
metadata for internal use:
*The first 16 bytes is the key GUID used by the system to find the proper
key from the key ring (opened keys), if there is no key that matches the
ciphertext GUID, the operation fails immediately returning NULL.
*The next 4 bytes is an internal version number, this value is hardcoded
for now, but can be potentially used in the future to distinguish what
version of the product was used to generate any given ciphertext.
*Then we have the randomly generated IV (8 bytes in case of DES family,
16 in case of AES) followed by the rest of the ciphertext + padding (up to a
whole block of padding, again 8 or 16 bytes depending on the algorithm).
The real plaintext is not purely the plaintext given to the builtin. It
includes an 8 byte header that describes some attributes of the plaintext an
d
the presence of the authenticator parameter for the builtin.
Let’s take a look to a sample:
CREATE SYMMETRIC KEY key1 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY PASSWORD
= 'k3y_1 P@.zzw0rd'
CREATE SYMMETRIC KEY key2 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY PASSWORD
= 'k3y_1 P@.zzw0rd'
go
OPEN SYMMETRIC KEY key1 DECRYPTION BY PASSWORD = 'k3y_1 P@.zzw0rd'
OPEN SYMMETRIC KEY key2 DECRYPTION BY PASSWORD = 'k3y_1 P@.zzw0rd'
go
SELECT key_name, convert(varbinary(100), key_guid) as key_guid_bin FROM
sys.openkeys
go
CREATE TABLE key_demo( key_guid_bin varbinary(100) , ciphertext
varbinary(8000) )
go
-- 3 times the same value with each key
INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
key_guid('key1'), 'AAAAAAAA' ))
INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
key_guid('key1'), 'AAAAAAAA' ))
INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
key_guid('key1'), 'AAAAAAAA' ))
INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
key_guid('key2'), 'AAAAAAAA' ))
INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
key_guid('key2'), 'AAAAAAAA' ))
INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
key_guid('key2'), 'AAAAAAAA' ))
go
SELECT * FROM key_demo
go
Let’s take a closer look to these results. In my case, I got the following
GUID on my demo key:
0x008FBF67275CF643BF623BC0422F7F5C
And the first ciphertext:
0x008FBF67275CF643BF623BC0422F7F5C010000
000EC0844ACA2346DB0FE2BD35FF93BEA656
B2A45296CB8E000F7094C2F21D0285
Notice that the GUID is indeed the prefix of the ciphertext, then we have
“01000000”, which is the hardcoded version.
0EC0844ACA2346DB – IV
0FE2BD35FF93BEA6 – 8 byte header
56B2A45296CB8E00 – cipher text (8 bytes)
0F7094C2F21D0285 – padding (8 bytes)
Now, let’s take a quick look and compare the next row:
0x008FBF67275CF643BF623BC0422F7F5C – same GUID (same key was used to encry
pt
this plaintext)
01000000 – same version
E4BA5A5141FE5842 – IV (different, and from this point the rest of the
ciphertext will be different as well)
0E4EEB8440877430 - header – (the plaintext for both headers should be the
same as the same options were used)
33C361724D74742F
26E6829B957E877F
I hope this helped to clarify the usage of the first 20 bytes of data as
well as the other 8 bytes used for the internal use header.
-Raul Garcia
SDE/T
SQL Server Engine
________________________________________
This posting is provided "AS IS" with no warranties, and confers no rights.
"coles.michael@.gmail.com" wrote:

> I just encrypted a bunch of rows in a table. I was a little surprised
> that encrypting N'A' resulted in a 44-byte result. N'AAAA' resulted in
> a 52-byte result. That indicates that 8-byte padding is being used.
> So the actual encrypted text of N'A' is only 8 bytes in length. What
> in the world are the other 36 bytes being used for? I noticed an
> interesting 'prefix' for the encrypted data:
> 0x0074126478AFB34BA54BA6280E2335EB010000
003AE8D337EF2E5B55BABDE251B245C1FC
CDB21C770073B642
> 0x0074126478AFB34BA54BA6280E2335EB010000
0057252C84D4B6C9F3FFA40444BB7E5ADC
509233F73548C7A8
> 0x0074126478AFB34BA54BA6280E2335EB010000
0077113F30B10CC2A8FD976628C98C06C5
F70E47AEEF3BE86C
> It appears the first 20 bytes of every encrypted string are exactly the
> same, regardless of the row, column, or data being encrypted. Further
> these 20 bytes appear to be generated when the OPEN ... KEY statement
> is issued. In between OPEN ... KEY statements, these first 20
> characters change, but for multiple encryptions after an OPEN ... KEY
> they remain the same.
> These first 20 - 36 bytes wouldn't happen to be the "Random IV" would
> they? One would think that storing the IV with the data would be
> potentially less secure than letting the user manage their own IV...
> What do you think?
>|||Thank you both for the info. That is quite a bit of extra storage when
encrypting small strings, but apparently that's just the cost of doing
business. I have another question for you - is it possible to invoke your
own implementation of an algorithm from SS 2005? For instance, if I created
my own CryptoAPI CSP can the T-SQL encryption functions recognize and use
it?
"Raul Garcia [MS]" <Raul Garcia [MS]@.discussions.microsoft.com> wrot
e in
message news:F7E85812-8C39-4E46-B8C0-C9B883BA06EC@.microsoft.com...[vbcol=seagreen]
> The first 20 bytes are not part of the ciphertext, it is really some
> metadata for internal use:
> *The first 16 bytes is the key GUID used by the system to find the
> proper
> key from the key ring (opened keys), if there is no key that matches the
> ciphertext GUID, the operation fails immediately returning NULL.
> *The next 4 bytes is an internal version number, this value is hardcoded
> for now, but can be potentially used in the future to distinguish what
> version of the product was used to generate any given ciphertext.
> *Then we have the randomly generated IV (8 bytes in case of DES family,
> 16 in case of AES) followed by the rest of the ciphertext + padding (up to
> a
> whole block of padding, again 8 or 16 bytes depending on the algorithm).
> The real plaintext is not purely the plaintext given to the builtin. It
> includes an 8 byte header that describes some attributes of the plaintext
> and
> the presence of the authenticator parameter for the builtin.
> Let's take a look to a sample:
> CREATE SYMMETRIC KEY key1 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY
> PASSWORD
> = 'k3y_1 P@.zzw0rd'
> CREATE SYMMETRIC KEY key2 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY
> PASSWORD
> = 'k3y_1 P@.zzw0rd'
> go
> OPEN SYMMETRIC KEY key1 DECRYPTION BY PASSWORD = 'k3y_1 P@.zzw0rd'
> OPEN SYMMETRIC KEY key2 DECRYPTION BY PASSWORD = 'k3y_1 P@.zzw0rd'
> go
> SELECT key_name, convert(varbinary(100), key_guid) as key_guid_bin FROM
> sys.openkeys
> go
> CREATE TABLE key_demo( key_guid_bin varbinary(100) , ciphertext
> varbinary(8000) )
> go
> -- 3 times the same value with each key
> INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
> key_guid('key1'), 'AAAAAAAA' ))
> INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
> key_guid('key1'), 'AAAAAAAA' ))
> INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
> key_guid('key1'), 'AAAAAAAA' ))
> INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
> key_guid('key2'), 'AAAAAAAA' ))
> INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
> key_guid('key2'), 'AAAAAAAA' ))
> INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
> key_guid('key2'), 'AAAAAAAA' ))
> go
> SELECT * FROM key_demo
> go
> Let's take a closer look to these results. In my case, I got the following
> GUID on my demo key:
> 0x008FBF67275CF643BF623BC0422F7F5C
> And the first ciphertext:
> 0x008FBF67275CF643BF623BC0422F7F5C010000
000EC0844ACA2346DB0FE2BD35FF93BEA6
56B2A45296CB8E000F7094C2F21D0285
> Notice that the GUID is indeed the prefix of the ciphertext, then we have
> "01000000", which is the hardcoded version.
> 0EC0844ACA2346DB - IV
> 0FE2BD35FF93BEA6 - 8 byte header
> 56B2A45296CB8E00 - cipher text (8 bytes)
> 0F7094C2F21D0285 - padding (8 bytes)
> Now, let's take a quick look and compare the next row:
> 0x008FBF67275CF643BF623BC0422F7F5C - same GUID (same key was used to
> encrypt
> this plaintext)
> 01000000 - same version
> E4BA5A5141FE5842 - IV (different, and from this point the rest of the
> ciphertext will be different as well)
> 0E4EEB8440877430 - header - (the plaintext for both headers should be the
> same as the same options were used)
> 33C361724D74742F
> 26E6829B957E877F
> I hope this helped to clarify the usage of the first 20 bytes of data as
> well as the other 8 bytes used for the internal use header.
> -Raul Garcia
> SDE/T
> SQL Server Engine
> ________________________________________
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "coles.michael@.gmail.com" wrote:
>|||No, right now, you cannot specify the CSP to be used for encryption. We're
looking at adding this capability in future versions of SQL Server.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike C#" <xyz@.xyz.com> wrote in message
news:u43EzXQoGHA.964@.TK2MSFTNGP05.phx.gbl...
> Thank you both for the info. That is quite a bit of extra storage when
> encrypting small strings, but apparently that's just the cost of doing
> business. I have another question for you - is it possible to invoke your
> own implementation of an algorithm from SS 2005? For instance, if I
> created my own CryptoAPI CSP can the T-SQL encryption functions recognize
> and use it?
>
> "Raul Garcia [MS]" <Raul Garcia [MS]@.discussions.microsoft.com> wr
ote in
> message news:F7E85812-8C39-4E46-B8C0-C9B883BA06EC@.microsoft.com...
>

"General network error. Check your network documentation." After Detach

Hello.
In our application we have to detach a database using a call from our
C# application. The problem is that after that we get random "General
Network Error" messages afterwards. This is all in a local call, both
the application server and the SqlServer are on the same machine.
I read about the Pooling = False switch but this is not an option for
us because this could have major repercusions on the rest of our
application.
Does anyone know how to get rid of this error without using the Pooling
= False?
Using .NET 1.1 and SqlServer 2000
ThanksAre you saying that you are killing database connections before the detach
and that causes the issues with your pooled connections? Is the detached
database specified as the 'Initial Catalog' specification in your connection
string?
If either or both are the case, I suggest you specify a different database
as the 'Initial Catalog' and change the database context to a different
database before closing the connection. That way, the polled connection can
remain open for reuse after the detach.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<escristian@.hotmail.com> wrote in message
news:1148589276.174672.171190@.y43g2000cwc.googlegroups.com...
> Hello.
> In our application we have to detach a database using a call from our
> C# application. The problem is that after that we get random "General
> Network Error" messages afterwards. This is all in a local call, both
> the application server and the SqlServer are on the same machine.
> I read about the Pooling = False switch but this is not an option for
> us because this could have major repercusions on the rest of our
> application.
> Does anyone know how to get rid of this error without using the Pooling
> = False?
> Using .NET 1.1 and SqlServer 2000
> Thanks
>|||The architecture I'm working with basically creates a new SqlConnection
everytime we make a call to the SqlService object (only way to access
database). To detach the database I put the database to offline mode
and then detach, here's the sql syntax I use:
USE master ALTER DATABASE <<dbName>> SET OFFLINE WITH ROLLBACK
IMMEDIATE
USE master EXEC sp_detach_db '<<dbName>>', 'true'
So our architecture will create a new connection before the 1st line of
sql, destroy that connection and then create another connection before
the 2nd call.
I will start reading on the connection pooling cause that seems to be
the cause of all these problems.
Thanks for your help|||Have you tried changing the database context to another database (e.g.
master) before closing the connection as I previously suggested? That way,
pooled connections won't be killed when you execute the ALTER
DATABASE...ROLLBACK.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<escristian@.hotmail.com> wrote in message
news:1148654269.887685.237720@.i39g2000cwa.googlegroups.com...
> The architecture I'm working with basically creates a new SqlConnection
> everytime we make a call to the SqlService object (only way to access
> database). To detach the database I put the database to offline mode
> and then detach, here's the sql syntax I use:
> USE master ALTER DATABASE <<dbName>> SET OFFLINE WITH ROLLBACK
> IMMEDIATE
> USE master EXEC sp_detach_db '<<dbName>>', 'true'
> So our architecture will create a new connection before the 1st line of
> sql, destroy that connection and then create another connection before
> the 2nd call.
> I will start reading on the connection pooling cause that seems to be
> the cause of all these problems.
> Thanks for your help
>

"General network error. Check your network documentation." After Detach

Hello.
In our application we have to detach a database using a call from our
C# application. The problem is that after that we get random "General
Network Error" messages afterwards. This is all in a local call, both
the application server and the SqlServer are on the same machine.
I read about the Pooling = False switch but this is not an option for
us because this could have major repercusions on the rest of our
application.
Does anyone know how to get rid of this error without using the Pooling
= False?
Using .NET 1.1 and SqlServer 2000
ThanksAre you saying that you are killing database connections before the detach
and that causes the issues with your pooled connections? Is the detached
database specified as the 'Initial Catalog' specification in your connection
string?
If either or both are the case, I suggest you specify a different database
as the 'Initial Catalog' and change the database context to a different
database before closing the connection. That way, the polled connection can
remain open for reuse after the detach.
Hope this helps.
Dan Guzman
SQL Server MVP
<escristian@.hotmail.com> wrote in message
news:1148589276.174672.171190@.y43g2000cwc.googlegroups.com...
> Hello.
> In our application we have to detach a database using a call from our
> C# application. The problem is that after that we get random "General
> Network Error" messages afterwards. This is all in a local call, both
> the application server and the SqlServer are on the same machine.
> I read about the Pooling = False switch but this is not an option for
> us because this could have major repercusions on the rest of our
> application.
> Does anyone know how to get rid of this error without using the Pooling
> = False?
> Using .NET 1.1 and SqlServer 2000
> Thanks
>