EncryptByCert Problem

I posted this message to the wrong group (sqlserver.programming), so I'm
reposting here. Sorry for the repost... it's late... so here goes:
OK, so I'm testing EncryptByCert with some code like the following:
DECLARE @.v varchar(8000);
SELECT @.v = REPLICATE('A', 117);
SELECT @.v;
DECLARE @.e varbinary(8000);
SELECT @.e = EncryptByCert(Cert_ID(N'TestCertificate'
), @.v);
SELECT @.e;
The function encrypts fine and I'm able to use DecryptByCert to get the
result. Problem is if I change the line SELECT @.v = REPLICATE('A', 117) to:
SELECT @.v = REPLICATE('A', 118);
The EncryptByCert function returns NULL every time with 118 or higher. BOL
states that the result is returned as a varbinary with a max length of
8,000. I keep getting a varbinary with a max length of 128. Can anyone
else reproduce this, or am I doing something wrong?
ThanksYes, this is a restriction of asymmetric key encryption - the actual data
that you can encrypt with one call will depend on the size of the private
key that is used.
For example: 512 bit RSA key can encrypt up to 53 bytes, 1024 bit up to 117
bytes, and 2048 bit up to 245 bytes.
See http://blogs.msdn.com/yukondoit/arc.../24/496521.aspx for a
workaround, if you really want to encrypt more.
Certificates should only be used to protect other keys, not to encrypt data
directly, so this limit does not impact the intended use of certificates.
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:fuaHg.586$k%3.406@.newsfe12.lga...
>I posted this message to the wrong group (sqlserver.programming), so I'm
>reposting here. Sorry for the repost... it's late... so here goes:
> OK, so I'm testing EncryptByCert with some code like the following:
> DECLARE @.v varchar(8000);
> SELECT @.v = REPLICATE('A', 117);
> SELECT @.v;
> DECLARE @.e varbinary(8000);
> SELECT @.e = EncryptByCert(Cert_ID(N'TestCertificate'
), @.v);
> SELECT @.e;
> The function encrypts fine and I'm able to use DecryptByCert to get the
> result. Problem is if I change the line SELECT @.v = REPLICATE('A', 117)
> to:
> SELECT @.v = REPLICATE('A', 118);
> The EncryptByCert function returns NULL every time with 118 or higher.
> BOL states that the result is returned as a varbinary with a max length of
> 8,000. I keep getting a varbinary with a max length of 128. Can anyone
> else reproduce this, or am I doing something wrong?
> Thanks
>|||Someone needs to tell the BOL writers to update the page on this then. It
currently indicates that EncryptByCert can return a varbinary up to 8,000
bytes in length, which is ludicrous if the input is really limited to 421
bytes and the output is limited to 432 bytes. I'm not advocating using
certificates and asymmetric keys to encrypt all data. I am advocating
accurate documentation of system limitations.
"Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in message
news:e2ASWY6xGHA.4336@.TK2MSFTNGP06.phx.gbl...
> Yes, this is a restriction of asymmetric key encryption - the actual data
> that you can encrypt with one call will depend on the size of the private
> key that is used.
> For example: 512 bit RSA key can encrypt up to 53 bytes, 1024 bit up to
> 117 bytes, and 2048 bit up to 245 bytes.
> See http://blogs.msdn.com/yukondoit/arc.../24/496521.aspx for a
> workaround, if you really want to encrypt more.
> Certificates should only be used to protect other keys, not to encrypt
> data directly, so this limit does not impact the intended use of
> certificates.
> 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:fuaHg.586$k%3.406@.newsfe12.lga...
>|||I'll ask for an update of these BOL topics to mention the restrictions
related to the key lenghts. If you access BOL online, you can also rate the
articles and submit feedback directly. Here are the links for the topics we
discussed:
http://msdn2.microsoft.com/en-us/library/ms188061.aspx
http://msdn2.microsoft.com/en-us/library/ms186950.aspx
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:%23vuAcc6xGHA.3500@.TK2MSFTNGP02.phx.gbl...
> Someone needs to tell the BOL writers to update the page on this then. It
> currently indicates that EncryptByCert can return a varbinary up to 8,000
> bytes in length, which is ludicrous if the input is really limited to 421
> bytes and the output is limited to 432 bytes. I'm not advocating using
> certificates and asymmetric keys to encrypt all data. I am advocating
> accurate documentation of system limitations.
> "Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in message
> news:e2ASWY6xGHA.4336@.TK2MSFTNGP06.phx.gbl...
>|||Thanks. I wanted to get verification and make sure it wasn't a problem with
my installation or how I was using the functions before I claimed errors in
either the application or the documentation.
"Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in message
news:Ovyex47xGHA.1288@.TK2MSFTNGP03.phx.gbl...
> I'll ask for an update of these BOL topics to mention the restrictions
> related to the key lenghts. If you access BOL online, you can also rate
> the articles and submit feedback directly. Here are the links for the
> topics we discussed:
> http://msdn2.microsoft.com/en-us/library/ms188061.aspx
> http://msdn2.microsoft.com/en-us/library/ms186950.aspx
> 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:%23vuAcc6xGHA.3500@.TK2MSFTNGP02.phx.gbl...
>|||Also, in the meantime, you may want to use the following reference:
http://msdn.microsoft.com/library/d...ryptencrypt.asp
The relevant paragraph extracted from above is:
The Microsoft Enhanced Cryptographic Provider supports direct encryption
with RSA public keys and decryption with RSA private keys. The encryption
uses PKCS #1 padding. On decryption, this padding is verified. The length of
plaintext data that can be encrypted with a call to CryptEncrypt with an RSA
key is the length of the key modulus minus eleven bytes. The eleven bytes is
the chosen minimum for PKCS #1 padding. The ciphertext is returned in
little-endian format.
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.
"Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in message
news:Ovyex47xGHA.1288@.TK2MSFTNGP03.phx.gbl...
> I'll ask for an update of these BOL topics to mention the restrictions
> related to the key lenghts. If you access BOL online, you can also rate
> the articles and submit feedback directly. Here are the links for the
> topics we discussed:
> http://msdn2.microsoft.com/en-us/library/ms188061.aspx
> http://msdn2.microsoft.com/en-us/library/ms186950.aspx
> 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:%23vuAcc6xGHA.3500@.TK2MSFTNGP02.phx.gbl...
>

encrypt database --

Hi,
Sorry for the message in spanish I confused the group.
Is there a way to encrypt a database (tables, columns, etc.) ? I want that
nobody can see the data model of my application
Probably it's impossible to deny access to the database, but at least if
someone is trying to copy my design he/she will see the name of objects,
columns, and others encrypted. (For example instead of see the table
ARTICLE, see symbols ="!!$%&/- )
I'll appreciate your comments.
Edmundo J. DavilaYou can assign certificates and encryption keys under the security
folder of a database in SQL Server Management Studio, however, these
only encrypt the data stream that is sent from a SQL Server instance
to any SQL Server Agents.
If you install SQL Server to run from inside of Microsoft Virtual
Server, then the entire database file will be stacker compressed which
would be unreadable to human eyes. But, with any kind of encryption,
SQL Server will have to spend time decrypting fields as it walks any
kind of search and this will cause a significant performance hit.

empty files to another files

Hi
Q1: I want to empty file into another file group. Is it possible?
Q2: What command does empty of the files. I do not see any command from
T-SQL doc but I may be wrong.
Q2: Is the table which are getting moved to another files are available for
transaction?
Thanks
Mangesh
Keep in mind that the primary file in the primary filegroup contains the
system table definitions and can not be moved. Moreover, no matter how many
other files or filegroups you have defined, the primary file can never be
removed from the database definition.
That being said, having multiple files within a filegroup will allow you to
mark any of these secondary files as EMPTYFILE and then have the data moved
to the remaining files within the same filegroup. To get the index data to
move, you could rebuild the indexes defined within the group or the
clustered index to move the data. You could also preform a database shrink
operation that would move data and index pages from one file to the others.
The only way to move index or data files from one filegroup to another, is
to rebuild the index(es) and define them to reside in the new filegroup.
This will only work on the user tables and indexes.
Sincerely,
Anthony Thomas

"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:F0A7AF64-0C5C-4AD8-BCAC-8C57B451B7F6@.microsoft.com...
Hi
Q1: I want to empty file into another file group. Is it possible?
Q2: What command does empty of the files. I do not see any command from
T-SQL doc but I may be wrong.
Q2: Is the table which are getting moved to another files are available for
transaction?
Thanks
Mangesh

empty files to another files

Hi
Q1: I want to empty file into another file group. Is it possible?
Q2: What command does empty of the files. I do not see any command from
T-SQL doc but I may be wrong.
Q2: Is the table which are getting moved to another files are available for
transaction?
Thanks
MangeshKeep in mind that the primary file in the primary filegroup contains the
system table definitions and can not be moved. Moreover, no matter how many
other files or filegroups you have defined, the primary file can never be
removed from the database definition.
That being said, having multiple files within a filegroup will allow you to
mark any of these secondary files as EMPTYFILE and then have the data moved
to the remaining files within the same filegroup. To get the index data to
move, you could rebuild the indexes defined within the group or the
clustered index to move the data. You could also preform a database shrink
operation that would move data and index pages from one file to the others.
The only way to move index or data files from one filegroup to another, is
to rebuild the index(es) and define them to reside in the new filegroup.
This will only work on the user tables and indexes.
Sincerely,
Anthony Thomas
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:F0A7AF64-0C5C-4AD8-BCAC-8C57B451B7F6@.microsoft.com...
Hi
Q1: I want to empty file into another file group. Is it possible?
Q2: What command does empty of the files. I do not see any command from
T-SQL doc but I may be wrong.
Q2: Is the table which are getting moved to another files are available for
transaction?
Thanks
Mangesh

empty files to another files

Hi
Q1: I want to empty file into another file group. Is it possible?
Q2: What command does empty of the files. I do not see any command from
T-SQL doc but I may be wrong.
Q2: Is the table which are getting moved to another files are available for
transaction?
Thanks
MangeshKeep in mind that the primary file in the primary filegroup contains the
system table definitions and can not be moved. Moreover, no matter how many
other files or filegroups you have defined, the primary file can never be
removed from the database definition.
That being said, having multiple files within a filegroup will allow you to
mark any of these secondary files as EMPTYFILE and then have the data moved
to the remaining files within the same filegroup. To get the index data to
move, you could rebuild the indexes defined within the group or the
clustered index to move the data. You could also preform a database shrink
operation that would move data and index pages from one file to the others.
The only way to move index or data files from one filegroup to another, is
to rebuild the index(es) and define them to reside in the new filegroup.
This will only work on the user tables and indexes.
Sincerely,
Anthony Thomas
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:F0A7AF64-0C5C-4AD8-BCAC-8C57B451B7F6@.microsoft.com...
Hi
Q1: I want to empty file into another file group. Is it possible?
Q2: What command does empty of the files. I do not see any command from
T-SQL doc but I may be wrong.
Q2: Is the table which are getting moved to another files are available for
transaction?
Thanks
Mangesh