Endless subqueries

I have a table with two columns: OID and Cumulative (witch is the same type as OID)

Each OID can have one or more Cumulatives.

Example of data:

OID Cumulative

167 292

167 294

167 296

168 292

169 302

169 304

The cumulation of each OID don't stop at one cumulation, but can be endless (theoretical).

Example: 167->292->590

So the table would have on more row:

OID Cumulative

295 505

I would like to represent this strucuture in a tree view and I'm looking for a query that could give me a table with this structure:

OID Cumul1 Cumul2 Cuml3 Cuml4 .... Cumuln

in the way I can read the row and have as many child nodes as I have values in the columns. The number of columns depends on the row with most cumulations.

How can I do the query?

Is there a better way as my table with n columns?

Thanks for suggestions

Your sample data is confusing..Can you fix it. Let us know the sample output from the input (sample data) you provided.

Encryption Varbinary Length

Using the new encryption included in SQL Server 2005, what is a good way to determine what length I should use for the column?

For example, I am encrypting a column, its maxlength is about 30 characters, but when encrypted, the encrypted value extends from between 50 and no more than 68 characters-

So if I had a column with a max of 500 or so characters, how could I know what varbinary length I should set it to if I were to encrypt it, without actually finding the highest value I could possibly fit into the field?

Is it good practice to just make it a varbinary(max) field?

-rob

My recommendation would be to create a dummy value of the maximum possible size (for example, on a varchar(500), create a 500 characters strings) and encrypt it with the same key and parameters you would normally use. That way you know you have reserved enough space to accept the maximum plaintext value your application accepts.

Example:

CREATE SYMMETRIC KEY key_demo WITH ALGORITHM = AES_256

ENCRYPTION BY PASSWORD = 'My 53Cr3+ p@.zzw0rD'

go

OPEN SYMMETRIC KEY key_demo

DECRYPTION BY PASSWORD = 'My 53Cr3+ p@.zzw0rD'

go

DECLARE @.MaxPlaintext nvarchar(500)

DECLARE @.Ciphertext varbinary(8000)

SET @.MaxPlaintext = replicate (N'a', 500)

-- Notice that I am using Unicode and the length is in bytes = 1000

PRINT datalength( @.MaxPlaintext )

-- Using the basic encryption parameters

SET @.Ciphertext = EncryptByKey( key_guid('key_demo'), @.MaxPlaintext )

-- Length using default parameters

PRINT datalength( @.Ciphertext )

-- Using the optional parameters

SET @.Ciphertext = EncryptByKey( key_guid('key_demo'), @.MaxPlaintext, 1, 'Dummy value' )

-- Length using optional parameters

PRINT datalength( @.Ciphertext )

go

I wrote an article (SQL Server 2005 Encryption – Encryption and data length limitations, http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx) that explains a little bit more about the encryption length limitations in SQL Server 2005. In that article I have a formula that may also help, but I would recommend using the method shown above instead of the formula in the article.

I hope I was able to help you. Please let us know if you have any further questions or feedback.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||This example will work for my needs - I should be able to get away with that as it is not often that a user will use the full length of a column. Thanks for validating.|||Raul, thanks for the solution on encrypting large amount of data. We have tried your approach on some BLOBs (e.g. files stored as binaries). The performance is very dismal though it works. Downloading a 3MB file that's encrypted took more than 45 seconds (the same file without encryption can be downloaded from the same webpage in 5 seconds). Any suggestions? I understand that SQL Server is not an efficient way to store files to begin with but we have to use it due to data center restrictions.|||

Hey Bob,

A lot of factors will impact the performance. Given the dramatic difference in times, I'm wondering if this is actually caused by the query you are using the retrieve the data? Are you only decrypting the blob or are other columns in the table encrypted as well?

I will run some tests on this, but if it is possible can you share the queries you are using with us? Or at least sample queries which approximate how you are retrieving the data?

Thanks,

Sung

|||

Sung:

There is only one field in the DB that's encrypted, the one containing the BLOB. The query is pretty much a copy of what Raul posted on his blog:

--
<edited to remove the source code sample>

|||

Unfortunately the work around I give is not very efficient with data that is larger than a few segments.

The main problem with this workaround (in terms of efficiency) is that the overhead SQL Server needs to pay besides the direct encryption/decryption (lookup for key in key ring, initialize key in cryptographic provider, verify inner header for consistency, etc.) will hit you multiple times for each blob. In the sample you mentioned of a 3MB file, you will be affected by this overhead more than 380 times.

Given the length and nature of the data you are manipulating, I would suggest using a CLR UDF instead and encrypt using CLR cryptographic APIs. The performance should be much better and also much more efficient in terms of space needed (not to mention a much cleaner code than my workaround).

I hope this information will help. Thanks a lot for your feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul, thanks for the reply. would a CLR UDF approach still use the SQL 2005's key/certificate infrstructure or it's completely .NET solution with separate keys? I have done file encryptions in .NET using X509 cert and symmetric keys, which are pretty easy to implement, but I'd like to leverage the keys created in SQL 2005 so the key management is consistent with other encrypted data in storage.

In addition, if you could show me some examples of using CLR UDF for SQL encryption that'd be great.

Thanks again

|||

Actually I was thinking of doing the whole key management and encryption directly in CLR due to the huge overhead you would have to pay for using the SQL Server infrastructure for large objects.

Another solution I can think of using SQL Server key management infrastructure would be to split the original plaintext in multiple rows and leverage on multiple client connections to encrypt/decrypt in parallel. For example: Create a table that will store the ciphertext as fragments in multiple rows (i.e. file_id, file_fragment_id used as primary key); your client can split the plaintext in segments (you could use a fixed length for the segments to make the file SEEK easier, let’s say 7K per segment) and using multiple connections send in parallel encrypt and insert requests to SQL Server (the fragment_id will be relative to the offset of the plaintext). As the fragments will be encrypted independently, they can also be decrypted independently and assembled again in the client application after decryption.

Hope this idea will work for you, or at least give you some other ideas.

-Raul Garcia

SDE/T

SQL Server Engine

Encryption Example

I am running through a great 2005 Encryption example at the following link:
http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx
I keep running into one problem though. When I execute the following at
this link
"open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
I get the following error
"Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'BY'."
Anybody have any idea what is going on?Below code (taken from Books Online) gives me a "proper" error message:
OPEN SYMMETRIC KEY SymKeyMarketing3
DECRYPTION BY CERTIFICATE MarketingCert9;
Server: Msg 15151, Level 16, State 1, Line 1
Cannot find the symmetric key 'SymKeyMarketing3', because it does not exist or you do not have
permission.
Perhaps the compatibility level for your database is lower than 90?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>I am running through a great 2005 Encryption example at the following link:
> http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx
> I keep running into one problem though. When I execute the following at
> this link
> "open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
> I get the following error
> "Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'BY'."
> Anybody have any idea what is going on?
>
>|||Thx for the idea, but it is 90.
"Tibor Karaszi" wrote:
> Below code (taken from Books Online) gives me a "proper" error message:
> OPEN SYMMETRIC KEY SymKeyMarketing3
> DECRYPTION BY CERTIFICATE MarketingCert9;
> Server: Msg 15151, Level 16, State 1, Line 1
> Cannot find the symmetric key 'SymKeyMarketing3', because it does not exist or you do not have
> permission.
> Perhaps the compatibility level for your database is lower than 90?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
> >I am running through a great 2005 Encryption example at the following link:
> > http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx
> > I keep running into one problem though. When I execute the following at
> > this link
> > "open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
> > I get the following error
> > "Msg 156, Level 15, State 1, Line 2
> > Incorrect syntax near the keyword 'BY'."
> > Anybody have any idea what is going on?
> >
> >
> >
>|||Are you running the release version of SQL Server 2005? This syntax was
different in some of the CTP versions.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>I am running through a great 2005 Encryption example at the following link:
> http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx
> I keep running into one problem though. When I execute the following at
> this link
> "open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
> I get the following error
> "Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'BY'."
> Anybody have any idea what is going on?
>
>|||No. I had just thought of that and noticed that I was on Beta 2. We've got
an MSDN subscription, so I'll get the latest and greatest. Thx.
"Roger Wolter[MSFT]" wrote:
> Are you running the release version of SQL Server 2005? This syntax was
> different in some of the CTP versions.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
> >I am running through a great 2005 Encryption example at the following link:
> > http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx
> > I keep running into one problem though. When I execute the following at
> > this link
> > "open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
> > I get the following error
> > "Msg 156, Level 15, State 1, Line 2
> > Incorrect syntax near the keyword 'BY'."
> > Anybody have any idea what is going on?
> >
> >
> >
>
>

Encryption Example

I am running through a great 2005 Encryption example at the following link:
http://blogs.msdn.com/lcris/archive.../16/504692.aspx
I keep running into one problem though. When I execute the following at
this link
"open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
I get the following error
"Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'BY'."
Anybody have any idea what is going on?Below code (taken from Books Online) gives me a "proper" error message:
OPEN SYMMETRIC KEY SymKeyMarketing3
DECRYPTION BY CERTIFICATE MarketingCert9;
Server: Msg 15151, Level 16, State 1, Line 1
Cannot find the symmetric key 'SymKeyMarketing3', because it does not exist
or you do not have
permission.
Perhaps the compatibility level for your database is lower than 90?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>I am running through a great 2005 Encryption example at the following link:
> http://blogs.msdn.com/lcris/archive.../16/504692.aspx
> I keep running into one problem though. When I execute the following at
> this link
> "open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
> I get the following error
> "Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'BY'."
> Anybody have any idea what is going on?
>
>|||Thx for the idea, but it is 90.
"Tibor Karaszi" wrote:

> Below code (taken from Books Online) gives me a "proper" error message:
> OPEN SYMMETRIC KEY SymKeyMarketing3
> DECRYPTION BY CERTIFICATE MarketingCert9;
> Server: Msg 15151, Level 16, State 1, Line 1
> Cannot find the symmetric key 'SymKeyMarketing3', because it does not exis
t or you do not have
> permission.
> Perhaps the compatibility level for your database is lower than 90?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>|||Are you running the release version of SQL Server 2005? This syntax was
different in some of the CTP versions.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>I am running through a great 2005 Encryption example at the following link:
> http://blogs.msdn.com/lcris/archive.../16/504692.aspx
> I keep running into one problem though. When I execute the following at
> this link
> "open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
> I get the following error
> "Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'BY'."
> Anybody have any idea what is going on?
>
>|||No. I had just thought of that and noticed that I was on Beta 2. We've got
an MSDN subscription, so I'll get the latest and greatest. Thx.
"Roger Wolter[MSFT]" wrote:

> Are you running the release version of SQL Server 2005? This syntax was
> different in some of the CTP versions.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>
>|||Below code (taken from Books Online) gives me a "proper" error message:
OPEN SYMMETRIC KEY SymKeyMarketing3
DECRYPTION BY CERTIFICATE MarketingCert9;
Server: Msg 15151, Level 16, State 1, Line 1
Cannot find the symmetric key 'SymKeyMarketing3', because it does not exist
or you do not have
permission.
Perhaps the compatibility level for your database is lower than 90?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>I am running through a great 2005 Encryption example at the following link:
> http://blogs.msdn.com/lcris/archive.../16/504692.aspx
> I keep running into one problem though. When I execute the following at
> this link
> "open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
> I get the following error
> "Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'BY'."
> Anybody have any idea what is going on?
>
>|||Thx for the idea, but it is 90.
"Tibor Karaszi" wrote:

> Below code (taken from Books Online) gives me a "proper" error message:
> OPEN SYMMETRIC KEY SymKeyMarketing3
> DECRYPTION BY CERTIFICATE MarketingCert9;
> Server: Msg 15151, Level 16, State 1, Line 1
> Cannot find the symmetric key 'SymKeyMarketing3', because it does not exis
t or you do not have
> permission.
> Perhaps the compatibility level for your database is lower than 90?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>|||Are you running the release version of SQL Server 2005? This syntax was
different in some of the CTP versions.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>I am running through a great 2005 Encryption example at the following link:
> http://blogs.msdn.com/lcris/archive.../16/504692.aspx
> I keep running into one problem though. When I execute the following at
> this link
> "open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
> I get the following error
> "Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'BY'."
> Anybody have any idea what is going on?
>
>|||No. I had just thought of that and noticed that I was on Beta 2. We've got
an MSDN subscription, so I'll get the latest and greatest. Thx.
"Roger Wolter[MSFT]" wrote:

> Are you running the release version of SQL Server 2005? This syntax was
> different in some of the CTP versions.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>
>sql

Encrypting Stored Procedure Code

Hi,
Is it possible to encrypt the code within a stored procedure in
Microsoft SQL Server?
My example is:
I've written a stored procedure. I don't want anyone to be able to
view the contents/code within this stored procedure unless I allow them
to see what is in it.
Thanks,
Darrin> Is it possible to encrypt the code within a stored procedure in
> Microsoft SQL Server?
Sure, but it is not very secure. A google search will yield plenty of
decryption algorithms. For example:
http://searchsqlserver.techtarget.c...1056869,00.html|||Once you encrypt a stored procedure you can not decrypt it (within SQL
Server)
So you can't show it to people who you want to show it to
example
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
But like Aaron said this is very weak, there are some vb apps out there
that will decrypt this
http://sqlservercode.blogspot.com/|||I know there are decryptors out there for SQL2000 but do you know of nay tha
t
have been written for SQL2005?
We encrypt our procs mainly as a safeguard against accidentally altering
them but do need a decryptor when we need to edit them. Our upgrade to
SQL2005 will be delayed untill we have a decryptor.
-- cranfield, DBA
"Aaron Bertrand [SQL Server MVP]" wrote:

> Sure, but it is not very secure. A google search will yield plenty of
> decryption algorithms. For example:
> [url]http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1056869,00.html[/url
]
>
>|||>I know there are decryptors out there for SQL2000 but do you know of nay
>that
> have been written for SQL2005?
I will admit that I have only spent 5 minutes looking, but I have yet to
find where the encrypted text is stored, since sys.sql_modules.definition,
sys.syscomments.ctext and object_definition() all return NULL. My guess is,
to make the encoding a little more obscure, that they stuff this into
mssqlsystemresource, or hide it in some obscure system view. So, you may be
able to get to it, you may not.

> We encrypt our procs mainly as a safeguard against accidentally altering
> them but do need a decryptor when we need to edit them.
Isn't that what source control is for? And doesn't that defeat the purpose
of encrypting them in the first place? If someone can accidentally alter a
production procedure, they can also do accidentally after using a decryption
method to view the text. Encryption does not, and will never, solve the
problem of lack of adherence to proper process. My suggestion is to correct
the process.
A|||Thanks for the response. Yes, I fully appreciate the importance of source
control and am confident that our dev department use it properly.
As a production DBA, though, looking after 100+ SQL Servers, when called at
2am to fix a performance "issue", the Decryptor is essential as you dont hav
e
the time to delve into VSS. Also its essential when you need to compare an
existing proc to a proc in Source Safe.
With the move to SQL2005, we will, as suggested, need to look at our
process. It may mean moving away from encrypting procs.
-- cranfield, DBA
"Aaron Bertrand [SQL Server MVP]" wrote:

> I will admit that I have only spent 5 minutes looking, but I have yet to
> find where the encrypted text is stored, since sys.sql_modules.definition,
> sys.syscomments.ctext and object_definition() all return NULL. My guess i
s,
> to make the encoding a little more obscure, that they stuff this into
> mssqlsystemresource, or hide it in some obscure system view. So, you may
be
> able to get to it, you may not.
>
> Isn't that what source control is for? And doesn't that defeat the purpos
e
> of encrypting them in the first place? If someone can accidentally alter
a
> production procedure, they can also do accidentally after using a decrypti
on
> method to view the text. Encryption does not, and will never, solve the
> problem of lack of adherence to proper process. My suggestion is to corre
ct
> the process.
> A
>
>

Encrypting data in SQL 2005

I have a pretty good understanding of how encryption works in 2005 but
what I am looking for is a real world example of how it has it has been
done. Here is what I am looking for, hopefully someone can point me in
the right direction.
Let's say that I have a table with one or more encrypted columns that
are encrypted by symmetric Key1. I want only authorized users to have
the ability to decrypt the data. I don't want DBA's or the DBO or the
developers to be able to do this.
I would think I would have to create Key1 using a password. But then
how do I protect that password to make sure the DBA's or developers
don't get hold of it.
My thought is that I could install something on the web server (a
encrypted password or certificate or something) that only the
application on that box could access, and then that is used to open
Key1 and decrypt the data.
Any thoughts or suggestions?
Just to clarify,the reason I would want to protect the data from the
DBA's is due to client requirements.
Thanks!
Jim"Jim Youmans" <jdyoumans@.gmail.com> wrote in message
news:1147899954.577587.108190@.i40g2000cwc.googlegroups.com...
>I have a pretty good understanding of how encryption works in 2005 but
> what I am looking for is a real world example of how it has it has been
> done. Here is what I am looking for, hopefully someone can point me in
> the right direction.
> Let's say that I have a table with one or more encrypted columns that
> are encrypted by symmetric Key1. I want only authorized users to have
> the ability to decrypt the data. I don't want DBA's or the DBO or the
> developers to be able to do this.
> I would think I would have to create Key1 using a password. But then
> how do I protect that password to make sure the DBA's or developers
> don't get hold of it.
> My thought is that I could install something on the web server (a
> encrypted password or certificate or something) that only the
> application on that box could access, and then that is used to open
> Key1 and decrypt the data.
> Any thoughts or suggestions?
> Just to clarify,the reason I would want to protect the data from the
> DBA's is due to client requirements.
>
Take a look at this blog entry
How Secure Is Your Data?
http://blogs.msdn.com/yukondoit/articles/480854.aspx
David|||I had read that but it does not answer the question of how to protect
the password. I don't want to code it into my stored procedure becase
then someone (a developer or DBA) would be able to look at the code and
then see it.
What I need is a suggestion for a system that allows the application to
get to the password but have that access to the password limited to the
app and the security group that put it on the box.
Any thoughts'
Thanks!
Jim|||If the admin really want to see your data there not a lot you can do about
it. The admin doesn't have to know the password because he can change it.
One of the SQL devs discusses this in his blog:
http://blogs.msdn.com/lcris/archive.../20/506187.aspx If you think
about it, you don't want to be able to block the admin. What if a
disgruntled employee decided to change a password only he could change and
you lost access to all the payroll data in the database? The best way to
limit access is to make sure you limit admin rights to people you can trust
because ultimately you have to trust somebody with the passwords. If you
decide only your CEO should have that power, I give you Enron. The only way
I can think of to block an administrator is to do the encryption and
decryption in your application so the admin has no access to the key.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Jim Youmans" <jdyoumans@.gmail.com> wrote in message
news:1147959831.765199.107920@.i39g2000cwa.googlegroups.com...
>I had read that but it does not answer the question of how to protect
> the password. I don't want to code it into my stored procedure becase
> then someone (a developer or DBA) would be able to look at the code and
> then see it.
> What I need is a suggestion for a system that allows the application to
> get to the password but have that access to the password limited to the
> app and the security group that put it on the box.
> Any thoughts'
> Thanks!
> Jim
>|||I agree. And we have a security group that would be in charge of the
password or key or whatever. My point is, I am looking for a system
that would work for what I have described.
What I would like to do is to have a cert of some type installed on the
web server that no one but the machine admin and the security group
would have access to. This cert would then be used by the web
application to access the key and decrypt the data. If a lead
developer or architect would need that access, then they would request
it through the proper channels with the security admin group and have
it installed on their machine in the same way.
I am not to concerned about the DBA or Admin hacking the box. This is
mostly to comply with a client security request. Only X people have
access to the sensitive data on the database.
Jim|||Why not have the application request the password when the authorized user
logs in to the database. The application can then open the key using that
password and use it for encryption/decryption. Finally, when the user
disconnects, the key should be closed. The key password would be needed in
addition to the credentials required for connecting to the database. As you
are not hardcoding it anywhere, a dbo would not be able to get to it.
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.
"Jim Youmans" <jdyoumans@.gmail.com> wrote in message
news:1147959831.765199.107920@.i39g2000cwa.googlegroups.com...
>I had read that but it does not answer the question of how to protect
> the password. I don't want to code it into my stored procedure becase
> then someone (a developer or DBA) would be able to look at the code and
> then see it.
> What I need is a suggestion for a system that allows the application to
> get to the password but have that access to the password limited to the
> app and the security group that put it on the box.
> Any thoughts'
> Thanks!
> Jim
>|||The application is a web app for checking on personal information
(think www.citibank.com). The app would need to decrypt private data
(like ssn or CC# or whatever) for display to the user. The user would
not have access (or knowledge) of the encryption method or anything
else.
Am I going down the wrong track here? Maybe I need to look at this
from a different angle?
Jim|||"Jim Youmans" <jdyoumans@.gmail.com> wrote in message
news:1147976240.393623.190210@.j73g2000cwa.googlegroups.com...
> The application is a web app for checking on personal information
> (think www.citibank.com). The app would need to decrypt private data
> (like ssn or CC# or whatever) for display to the user. The user would
> not have access (or knowledge) of the encryption method or anything
> else.
> Am I going down the wrong track here? Maybe I need to look at this
> from a different angle?
>
You can store the password in your application's configuration. Then secure
that using the OS.
There is specific tooling to make this easy in asp.net 2.0 applications.
How To: Encrypt Configuration Sections in ASP.NET 2.0 Using DPAPI
http://msdn.microsoft.com/library/d... />
000005.asp
How To: Encrypt Configuration Sections in ASP.NET 2.0 Using RSA
http://msdn.microsoft.com/library/d... />
000006.asp
Otherwise you need to use the crypto API stuff to put the password (or a key
to encrypt it) in the OS secure store.
Davidsql

Encrypted data size by original size, algorithm ?

I want to know encrypted data's size for designing database field size.

For example, cardnumber varchar(20) Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.

I think the size does not depend to PassPhrase char length.

Regards,
Yoshihiro Kawabata

The simplest way is to just encrypt the largest piece of data that you will store using the encryption algorithm of your choice and take note of the size of the resulting blob. That will be the size of the field. There is also a formula that allows you to compute this, but it also includes the size of a header which might expand in future versions of SQL Server. I suggest to always leave several bytes more to account for changes in the format of encrypted data.

Thanks
Laurentiu

|||

Here's a post that describes how to determine the length of the encrypted data for SQL Server 2005:

http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx

Thanks
Laurentiu

Encrypted data size by original size, algorithm ?

I want to know encrypted data's size for designing database field size.

For example, cardnumber varchar(20) Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.

I think the size does not depend to PassPhrase char length.

Regards,
Yoshihiro Kawabata

The simplest way is to just encrypt the largest piece of data that you will store using the encryption algorithm of your choice and take note of the size of the resulting blob. That will be the size of the field. There is also a formula that allows you to compute this, but it also includes the size of a header which might expand in future versions of SQL Server. I suggest to always leave several bytes more to account for changes in the format of encrypted data.

Thanks
Laurentiu

|||

Here's a post that describes how to determine the length of the encrypted data for SQL Server 2005:

http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx

Thanks
Laurentiu

empty status of a variable

The example bellow will receive a parameter.

create procedure usp_InsertProducts
@.SKU varchar(30)

Now how do I check whether is @.SKU empty or not.

Your help is kindly appreciated.

Regards

Eugene Anthony

*** Sent via Developersdex http://www.developersdex.com ***> how do I check whether is @.SKU empty or not.

IF @.sku IS NULL ...

or

IF @.sku = '' ...

depending on what you mean by "empty".

To differentiate between a NULL and no value specified you could
provide a default using some invalid token to represent the missing
value:

CREATE PROCEDURE usp_InsertProducts
@.sku VARCHAR(30) = '<Unspecified>'
AS ...

IF @.sku = '<Unspecified>'
...

--
David Portas
SQL Server MVP
--