Encrypting Text

In SQL2K, I used to use the PWDENCRYPT() and PWDCOMPARE() to encrypt the
password and then compare when the user logs in.
In SQL 2005, seems that this function is not working, and I don't understand
the Symmetric key function. Can anyone please kindly give me some example?
Thanks in advance.
Regards,
Janet[url]http://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp[/
url]
"Janet >" wrote:

> In SQL2K, I used to use the PWDENCRYPT() and PWDCOMPARE() to encrypt the
> password and then compare when the user logs in.
> In SQL 2005, seems that this function is not working, and I don't understa
nd
> the Symmetric key function. Can anyone please kindly give me some example
?
> Thanks in advance.
> Regards,
> Janet
>
>

Encrypting passwords

A friend of my self asked me how he can save a password not as clear text. He wanted to encrypt the password and save the encrypted string in the database.

How can he do this? Maybe somebody can help me here.

Regards Markus

What does your friend need the password for? If it is used for authentication (to verify that another password submitted is matching the password stored in the database), then you can hash the password. If you need to store the password to use it somewhere else in clear form, then you need to encrypt it.

For SQL Server 2000 there are no builtin functions for hashing or encryption You may hear about pwdencrypt - an undocumented function - do not use it. For SQL Server 2000, you will have to write your own extended procedures for performing encryption or hashing.

In SQL Server 2005, you can use HashBytes to hash the password and EncryptByKey to encrypt it.

Thanks
Laurentiu

|||

but is this truly secure? you're still sending the password over a connection in clear text, unless you're in SSL, yes? isn't it best to simply hash the password on the clientside to begin with?

Laurentiu Cristofor wrote:

In SQL Server 2005, you can use HashBytes to hash the password and EncryptByKey to encrypt it.

Thanks
Laurentiu

|||

It is secure if the connection is secured using SSL - it should be secured that way if you're concerned about security.

Hashing on the client side does not address the insecure connection problem, because your authentication will then only depend on the hash (that's all the server will see from the client), and then the hash will effectively serve the same role as the password, so if a hash is intercepted on an insecure connection, a third party can pass it back to the server and connect this way.

Also, note that in my previous post I did not recommend implementing custom authentication schemes using those functions. Instead, you should leverage the mechanisms already provided by SQL Server.

Thanks
Laurentiu

sql

Encrypting non text data

I want to encrypt some audio files before inserting them in a SQL server database. I want to use the built in sql server encryption. I found that the encrypt by key function accepts only text (nchar,nvarchar,etc).
I was thinking or reading the audio file's bytes and change them to a string and then insert them in the database. I just wanted to take opinions here. Is there a better way to do this?
thanks in advance

Actually, the encryption functions are really designed for binary data, but we accept text variables directly for usability reasons.

Unfortunately you will hit a different problem because of a design limitation on our current implementation: the output cannot be greater than 8k, therefore the plaintext has to be < 8K.

I wrote an article describing the data length limitations and a TSQL-based workaround (http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx), but the workaround proposed in this article has some limitations itself. I would also suggest considering using a CLR module to encrypt/decrypt large BLOBs.

I hope this information will be useful. We appreciate your feedback on this area.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Encrypting column values

I want to store a function in SQL Server database that when called during insertion, encrypts a value and then stores encrypted text in the column.

Is there any built in Encrypting and Hashing mechanism in SQL 2005.

There is very 'rich' encryption capabilities in SQL 2005.

You may wish to start by referring to Books Online, Topic: Cryptographic Functions

encrypting a text file

I have programs written in sas. The programs themselves can be treated like a text file for this purpose. I need a program that will encrypt my program then when needed decyrypt it storing it into a temp file, run it and then delete it.

any thoughts or anything out there to use.

NicoleAny ability to decrypt means potential weakness thus vulnerability. Look into SQLShield, it may help.

EncryptByKey Function always returning null

When I use EncryptByKey function to encrypt text using AES 128 bit key, I get always null result. this is how I do the encryption:

ALTER PROCEDURE [dbo].[ProcMyProc](@.ClearText nvarchar(50))
AS
BEGIN

OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD= 'MY_Password_128';

Declare @.Temp varbinary(8000);

Set @.Temp =EncryptByKey(Key_GUID('MyKey'),@.ClearText);

close symmetric key MyKey;

select @.Temp as temp;
END

The result I get for this procedure is null. Is there something wrong with this code?

Hey Muhammad,

I tried out your code and it seems to work for me. Can you try executing your script outside of the proc? You can add in "select * from sys.openkeys" after your open statement just to check to see if the key was actually opened.

Sung

|||

Also, if this still doesn't work, can you let us know what OS version you are using? Is it XP, 2000, or 2003?

Thanks
Laurentiu

|||I found the cause of the problem. I was misspelling the name of the key when passing it to the Key_GUID() function. but shouldn't this give an error saying there's no key with this name instead of just returning null?|||

Hi Muhammad,

The way our built-ins work is that they return null instead of returning an error. I don't remember why exactly this is the case, but I think it has to do with backwards compatibility and parsing logic of the built-ins.

Sung

Encrypt By Key gives different results every call time

Every time I call Encrypt by key I get a different result, although I am encrypting the same clear text,using the same authenticator and using the same key.
I tried calling it twice inside one stored procedure and I got two different results.

Declare @.Temp varbinary(8000);
Declare @.Temp2 varbinary(8000);
set @.Temp = EncryptByKey(Key_GUID('MyKey'),@.cleartext,1,@.Authenticator);
set @.Temp2= EncryptByKey(Key_GUID('MyKey'),@.cleartext,1,@.Authenticator);
Select @.Temp,@.Temp2;

when executing this stored procedure, the value of @.Temp is differnent from the result of @.Temp2, and the same thing happens without using an authenticator.

Is there something going behind in the encryption mechanism of SQL server?

yes it's deliberate and important. You might have 2 rows with the same value, for example a salary. If these had the same resulting encrypted value it would make discovery of the non-encrypted value much easier. To address this SQL 2005's encryption methods use a salt with a random initialisation vector so you get a different encrypted value for the same input text.

thx,

Simon.

encrypt a text or varchar(max) field

I understood that sql 2005 has EncryptByCert(varchar) function to encrypt data field. but varchar is limit by 8,000 chars long.
My question is:
as sql 2005 built in function, is there any way to encrypt a text or varchar(max) fieild which could be more than 8,000 chars?
Thanks
duohong

It is not recommended to encrypt data directly using certificates; instead, it is recommend to use symmetric keys to encrypt data, and certificates can be used to encrypt those symmetric keys. One reason for this is that asymmetric key encryption is much slower than symmetric key encryption. Also, asymmetric key algorithms are particularly good for key management, but are not well suited for general data encryption, which is why asymmetric and symmetric keys are in practice used together, the first for allowing secure key exchange and the second for the actual data encryption. In SQL Server 2005, the main purpose of certificates is for signing modules and for encrypting other keys, not for directly encrypting data.

Also, note that certificates are currently generated with 1024 bit length private keys and the length of data that you can encrypt with such a certificate is at most 117 bytes (1024/8 - 11 for padding). This is less than the varchar limit of 8000 characters, but is sufficient for the intended use of certificates.

Thanks
Laurentiu

Encription of a column

I need to replicate some Oracle functionality that allows
a text column to be encripted in the database and
decripted by a JSP web application. Anyone have any
suggestions on how to go about this?You can find several third party products that have encryption options
listed in the Encryption section of the faq at:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
-Sue
On Thu, 10 Jun 2004 09:21:55 -0700, "Amy Thropp"
<athropp@.thinksmartps.com> wrote:

>I need to replicate some Oracle functionality that allows
>a text column to be encripted in the database and
>decripted by a JSP web application. Anyone have any
>suggestions on how to go about this?|||SQL Server 2000 does not natively support in-database cryptographic
operations. It has been announced by Microsoft that SQL Server 2005 will
support encryption and key management. One article that reports on this
announcement can be found @.
http://www.internetnews.com/dev-new...cle.php/3358991
Mark Shlimovich
"Amy Thropp" <athropp@.thinksmartps.com> wrote in message
news:239401c44f07$0bb652c0$7d02280a@.phx.gbl...
> I need to replicate some Oracle functionality that allows
> a text column to be encripted in the database and
> decripted by a JSP web application. Anyone have any
> suggestions on how to go about this?|||Have a look at
www.whamware.com - Whamware.Crypt
www.appsecinc.com - dbEncrypt
www.netlib.com - Encryptionizer for SQL Server
Tom
"Amy Thropp" wrote:
> I need to replicate some Oracle functionality that allows
> a text column to be encripted in the database and
> decripted by a JSP web application. Anyone have any
> suggestions on how to go about this?|||www.xpcrypt.com

Encoding string from MS SQL

Hi all,

I have an application which will send out email in plain text in multi langauage.
the email content will be pull from txt file save in UTF-8.
i can send out email from the template with the encoding.
but when i insert data from the SQl server. the data from the SQL server are not encoded.
how do i encode the data (in other lanagauge) from sql server into UTF-8 so that it can be send together with the template.

I have try changing the data into byte and encode it in UTF-8.
but it won't displayed correctly. pls help. thanks

Unicode in SQL Server is either UCS-2 or UTF 16 and the later is the generally used version, .NET is UTF 16 by default so you can change your .NET encoding to UTF 16. The reason is NChar, NVarchar, NText, NChar max and NVarchar max are multi bytes by definition so you just need to convert to UTF 8 in your code. Hope this helps.|||

my data from the database look like this ??òú??. it is chinese simplified gb2313
the datatype for the fields is varchar.
so how do i encode it into UTF8 and it can be display in chinese.
pls help i'm totally confuse by the encoding.

|||

The Chinese you are using is Windows code page, there are six Chinese collation in SQL Server you have to find the right one in the thread below. And to UTF 8 encode in VS you start at the link below it is for VS2003 but I think it should work. So you do column level collation for your specific Chinese in SQL Server and do Unicode encoding in VS and it may be resolved. Hope this helps.

http://forums.asp.net/1067798/ShowPost.aspx

http://www.aspnetresources.com/blog/unicode_in_vsnet.aspx

|||

Hi Thanks for you advice,

base on your info i manage to find the extended proc to solve my prob xp_cp2u_web.
but i have another prob. in my stored procedure the output parameter i set it to a size of 20 data type nvarchar. it will return the result. and it will display correctly on screen. but when it is use to send via email. the rest of the content in the email is gone after my chinese character.

after some debugging, i found that it is due to the size of nvarchar i set. my chinese character size is 3. so if i set the nvarchar size to 3 all will work but if the size i set is bigger than the actual result return. it will affect the rest of the text in my email..

can anyone pls advise me on how to set the nvarchar size for my output sqlparameter.
is there a way where i can set the size of the Nvarchar as dynamic. Thanks

cmd5.CommandText = "GetEmailDetail";
cmd5.CommandType = CommandType.StoredProcedure;
cmd5.Parameters.Add("@.No", Service_ID);
SqlParameter parameterfullname = cmd5.Parameters.Add("@.name", SqlDbType.NVarChar, 20);
parameterfullname.Direction = ParameterDirection.Output;

|||

Try the link below everything you need is covered including the correct stored procedure because that is important. When you are getting value back from a SQL Server stored procedure it is OUTPUT parameter except INT which is return value, so if in doubt always use OUTPUT if it is not needed SQL Server will ignore it. So use correct column level collation and ADO.NET OUTPUT parameters. Hope this helps.

http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/

encoding problem

Hi
I use Oracle data source. The report is in Polish. When I try display some text from database or use report parameters, this text is not written correctly. The environment on the machine, where is installed Reporting Services is rather correct, when I am using SQL*PLUS it is ok. I linked Oracle database to MSSQL, I used Query Analyzer, the text was written correct in Polish. What to do?
morganWhat is the report language set to?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"morgan" <morgan@.discussions.microsoft.com> wrote in message
news:9E40E53A-C040-470F-89E2-0E9807BA98D7@.microsoft.com...
> Hi
> I use Oracle data source. The report is in Polish. When I try display some
text from database or use report parameters, this text is not written
correctly. The environment on the machine, where is installed Reporting
Services is rather correct, when I am using SQL*PLUS it is ok. I linked
Oracle database to MSSQL, I used Query Analyzer, the text was written
correct in Polish. What to do?
> morgan|||Polish is the report language. By I used English ( US) too.
morgan
"Ravi Mumulla (Microsoft)" wrote:
> What is the report language set to?
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "morgan" <morgan@.discussions.microsoft.com> wrote in message
> news:9E40E53A-C040-470F-89E2-0E9807BA98D7@.microsoft.com...
> > Hi
> >
> > I use Oracle data source. The report is in Polish. When I try display some
> text from database or use report parameters, this text is not written
> correctly. The environment on the machine, where is installed Reporting
> Services is rather correct, when I am using SQL*PLUS it is ok. I linked
> Oracle database to MSSQL, I used Query Analyzer, the text was written
> correct in Polish. What to do?
> >
> > morgan
>
>

Enabling full text search on existing sql server instance

Hi

I have an existing instance of sql express 2005 w/advanced services installed. When I installed I did not install full text search. Is there a way I can enable full text search on this existing instance?

Thanks

Try this:

sp_configure 'user instances enabled', '0'

sp_fulltext_database 'enable'

Then go ahead and try create your catalogs and indexes as normal.

Enabling Full Text Search Feature in MSSQL

We want to use the full text search feature of MS SQL, for this we need to create the Full-Text catalogs for our databases. How can i do that.
ThanksCombine this link with what is in BOL and you are in business

http://www.databasejournal.com/features/mssql/article.php/1438211

HTH

Enabling Full Text

Is it possible to enable full text indexing on SQL Server without
re-installing (assuming that the full text option was not selected when SQL
was first installed)?
Dirk,
Yes. However, it depends upon the SQL Server version (7.0, 2000 or now 2005)
that you are using... Could you post the full output of -- SELECT
@.@.version -- as this is very helpful info in troubleshooting SQL FTS issues.
FYI, SQL Server 2000 installs the Full-text Search (FTS) components by
default, while SQL Server 7.0 doesn't, but you can use your SQL 7.0 CD and
use "custom installation" and under the server components, select "Full-text
Search".
Regards,
John
"Dirk" <Dirk@.discussions.microsoft.com> wrote in message
news:8A7F6B05-3486-44E1-8763-C1F471B053FA@.microsoft.com...
> Is it possible to enable full text indexing on SQL Server without
> re-installing (assuming that the full text option was not selected when
SQL
> was first installed)?
|||Here it is:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
"John Kane" wrote:

> Dirk,
> Yes. However, it depends upon the SQL Server version (7.0, 2000 or now 2005)
> that you are using... Could you post the full output of -- SELECT
> @.@.version -- as this is very helpful info in troubleshooting SQL FTS issues.
> FYI, SQL Server 2000 installs the Full-text Search (FTS) components by
> default, while SQL Server 7.0 doesn't, but you can use your SQL 7.0 CD and
> use "custom installation" and under the server components, select "Full-text
> Search".
> Regards,
> John
>
> "Dirk" <Dirk@.discussions.microsoft.com> wrote in message
> news:8A7F6B05-3486-44E1-8763-C1F471B053FA@.microsoft.com...
> SQL
>
>
|||Dirk,
I stand corrected... "SQL Server 2000 installs the Full-text Search (FTS)
components by default," should of been "SQL Server 2000 installs the
Full-text Search (FTS) components by default for Standard Edition and
above". Since, you're using Developer Edition (and THAT is why I asked for
the @.@.version info!), this edition does NOT install the FTS components by
default and you can use the same procedures as I described for SQL Server
7.0...
Basically, you can use your SQL 2000 Developer Edition CD and run setup and
select "custom installation" and under the server components select
"Full-text Search" and the CD will install these components for you. Note,
if you have applied any Service Pack (SPx) to your Developer Edition, you
will need to re-install the SP as well so that any FTS &/or MSSearch fixes
can be applied.
Regards,
John
"Dirk" <Dirk@.discussions.microsoft.com> wrote in message
news:C0C7237E-BDAA-47CD-95E7-47A5476C0841@.microsoft.com...
> Here it is:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows[vbcol=seagreen]
> NT 5.0 (Build 2195: Service Pack 4)
>
> "John Kane" wrote:
2005)[vbcol=seagreen]
issues.[vbcol=seagreen]
and[vbcol=seagreen]
"Full-text[vbcol=seagreen]
when[vbcol=seagreen]

Empty spaces in the detail row..

How can I get rid of empty spaces following the text value? This is
happening for a Comments field, the text is longer and RS seems to add some
extra blank lines to the row.
i have set both the properties of the cell to "Can Increase to accomodate
contents" and "Can decrease to accomodate contents."
and using Trim () to trauncate any spaces but still does not work.
Also, is there any way to merger 2 cells vertically?You need to make the initial size of the row / textbox as small as possible.
Vertical merging of cells is not supported in the current version.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"newmem" <""> wrote in message news:eCgyAPJWEHA.2844@.TK2MSFTNGP11.phx.gbl...
> How can I get rid of empty spaces following the text value? This is
> happening for a Comments field, the text is longer and RS seems to add
> some
> extra blank lines to the row.
> i have set both the properties of the cell to "Can Increase to accomodate
> contents" and "Can decrease to accomodate contents."
> and using Trim () to trauncate any spaces but still does not work.
> Also, is there any way to merger 2 cells vertically?
>