how to make sensitive data encrypted in tables

Any idea how to make sensitive data encrypted and make it readable for
applications which uses it. I mean like password in syslogins table. Is
there a way to do it in sql server or do i need to use any third party
tools. Can you please list out if there are any tools.
Thanks,
Subbu.Encryption is available in SQL server 2005.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:OcHijBLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
Any idea how to make sensitive data encrypted and make it readable for
applications which uses it. I mean like password in syslogins table. Is
there a way to do it in sql server or do i need to use any third party
tools. Can you please list out if there are any tools.
Thanks,
Subbu.|||See if this helps:
http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
Which version of SQL Server are you using?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:OcHijBLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
Any idea how to make sensitive data encrypted and make it readable for
applications which uses it. I mean like password in syslogins table. Is
there a way to do it in sql server or do i need to use any third party
tools. Can you please list out if there are any tools.
Thanks,
Subbu.|||We are using SQL Server 2000.
Thanks for the responses.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23B%23XkILiGHA.1276@.TK2MSFTNGP03.phx.gbl...
> See if this helps:
> http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
> Which version of SQL Server are you using?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:OcHijBLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Any idea how to make sensitive data encrypted and make it readable for
> applications which uses it. I mean like password in syslogins table. Is
> there a way to do it in sql server or do i need to use any third party
> tools. Can you please list out if there are any tools.
> Thanks,
> Subbu.
>
>|||Then you'll have to encrypt the data before storing it in SQL Server. Check
out:
http://msdn.microsoft.com/library/d...ction-dpapi.asp
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:O0y$STLiGHA.2220@.TK2MSFTNGP05.phx.gbl...
We are using SQL Server 2000.
Thanks for the responses.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23B%23XkILiGHA.1276@.TK2MSFTNGP03.phx.gbl...
> See if this helps:
> http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
> Which version of SQL Server are you using?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:OcHijBLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Any idea how to make sensitive data encrypted and make it readable for
> applications which uses it. I mean like password in syslogins table. Is
> there a way to do it in sql server or do i need to use any third party
> tools. Can you please list out if there are any tools.
> Thanks,
> Subbu.
>
>

how to make sensitive data encrypted in tables

Any idea how to make sensitive data encrypted and make it readable for
applications which uses it. I mean like password in syslogins table. Is
there a way to do it in sql server or do i need to use any third party
tools. Can you please list out if there are any tools.
Thanks,
Subbu.Encryption is available in SQL server 2005.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:OcHijBLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
Any idea how to make sensitive data encrypted and make it readable for
applications which uses it. I mean like password in syslogins table. Is
there a way to do it in sql server or do i need to use any third party
tools. Can you please list out if there are any tools.
Thanks,
Subbu.|||See if this helps:
http://www.sqlsecurity.com/FAQs/SQLServerFAQ/tabid/55/Default.aspx
Which version of SQL Server are you using?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:OcHijBLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
Any idea how to make sensitive data encrypted and make it readable for
applications which uses it. I mean like password in syslogins table. Is
there a way to do it in sql server or do i need to use any third party
tools. Can you please list out if there are any tools.
Thanks,
Subbu.|||We are using SQL Server 2000.
Thanks for the responses.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23B%23XkILiGHA.1276@.TK2MSFTNGP03.phx.gbl...
> See if this helps:
> http://www.sqlsecurity.com/FAQs/SQLServerFAQ/tabid/55/Default.aspx
> Which version of SQL Server are you using?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:OcHijBLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Any idea how to make sensitive data encrypted and make it readable for
> applications which uses it. I mean like password in syslogins table. Is
> there a way to do it in sql server or do i need to use any third party
> tools. Can you please list out if there are any tools.
> Thanks,
> Subbu.
>
>|||Then you'll have to encrypt the data before storing it in SQL Server. Check
out:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsecure/html/windataprotection-dpapi.asp
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:O0y$STLiGHA.2220@.TK2MSFTNGP05.phx.gbl...
We are using SQL Server 2000.
Thanks for the responses.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23B%23XkILiGHA.1276@.TK2MSFTNGP03.phx.gbl...
> See if this helps:
> http://www.sqlsecurity.com/FAQs/SQLServerFAQ/tabid/55/Default.aspx
> Which version of SQL Server are you using?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:OcHijBLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Any idea how to make sensitive data encrypted and make it readable for
> applications which uses it. I mean like password in syslogins table. Is
> there a way to do it in sql server or do i need to use any third party
> tools. Can you please list out if there are any tools.
> Thanks,
> Subbu.
>
>

how to make password field case sensitive in sql server 2005

Hi,

SELECT UserID, UserName, Password, PublisherID, Currency
FROM [User]
WHERE (Password = 'Anitha') I am using the above mentioned it is working but int the password field i had given it as anitha. Now the querry is retriving the record for anitha, it shouldnot happen. The querry should retrive the record of anitha only for where condition anitha and not for Anitha or ANITHA etc..

Thanks

Vishwanath

Convert the password field and password value to varbinary and compare if they are equal.

Here is the code:

select

*from userswhere UserName='Nilesh'Andcast(passwordasvarbinary(20))=cast(N'password'asvarbinary(20))

The usename is not case sensitive but password is.

|||

hi Vishwanath,

its good practise to store passwords in encoded/encrypted format. what i do is use a custom encryption function which uses our own logic to encrypt each character and store it.

to add what Nilesh has mentioned read this linkhttp://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

Hope it helps.

regards,

Satish.

|||

Hi satish,

now its working fine, but when i choose the collation property from that password field and changed it to bin format then at that it gave some warning message saying that some of the data will be lost, but when i choose yes i couldnt experience any such loss of data. Everything is working fine..

Thnaks

Vishwanath

|||

ohk sounds cool.

please mark the post which helped you as answered, it will give credit to answerer and post will be resolved.

thanks,

satish.

how to make Enforce Password Policy unchecked by default

I am trying to create a login using my application.

The application creates the user and password itself .

when working with sql server 2000 it works fine.

when i try to install my application with sql server 2005 it is displaying the error .

'Password does not meet windows policy requirements

because it is not complex enough'

I want to keep the same password.

Is there any way to disable/uncheck this option by default?

Prashant

You could modify the login creation script to specify CHECK_POLICY=OFF

USE [master]

GO

CREATE LOGIN [TestLogin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

|||

This is good but for this i need to make changes in my code .My application integrates with sql server 2000,MSAccess ,Oracle.

the code for creating login is same .So can it be possible at sql server level to make Check_policy =OFF as default option.

|||I would be very interested to know this as well. I have a similar issue. If your SQL 2005 server is not part of a domain that enforces group security policies, one option you do have is to relax the default Local Security Policy on the 2003 server upon which your SQL 2005 is running. Using the "Local Security Policy" editor found in Administive Tools you can select "Password Policy" under Account Policies. From there you can change the settings for history, age, length, complexity requirements, and encryption. You will want to turn off the "Password must meet complexity requirements". You might have to change some of the other settings to meet your specific application needs.|||

I would be interested in this as well.

We have a third party Application that the client says works with SQL Server 2005, but the user logon creation they have built in dos not pass a correct password, and turning the poicy off may fix the problem.

|||No, many features which are implemeted in the engine cannot be changed by default:

CREATE LOGIN(...)

CHECK_POLICY = { ON | OFF }

Applies to SQL Server logins only. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.

Maybe you post a bug / feature request on the connect forums to make it happen in further versions.

Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

How to make DB2 Connection String Dynamic, Password Problem

Hi All,

The problem I am facing is related to dynamic configuration of package one of the package connection is DB2 connection, I tried to set the expression connection string for that connection to the variable which contains the connection string to the DB2 but when I set connection the String property then i get the error message in transformation that password is missing, I dont want to write password in connection String for security reasons so I tried to save password in connection which is not helpful I am getting the same error message package security setting I changed to "Encrypt Sensitive Data with User Key" , anywayout to overcome this problem?

Thanks,

Manoj Kumar

Try setting ProtectionLevel to "SaveSensitiveWithPassword".

If you are using a configuration file to set the connection string, you can edit the .dtsconfig file directly to add the password into the connection string, but you should make sure the config file is stored in a secure location if you do this.

|||

I am using configuration setting and its based on (XML File and Table) XML Files points the configuration Database and Table stores the all configuration information so If I have to append the password configuration Table entry need to be changed but this is not required I am dealing with some sensitive data so they dont want the password of that db user stored somewhere exposed, some of the few questions related to this which I wanted to ask are as under.

1)If I Save password in Package then is there anywayout to bypass the password part from connection setting means package take password from the saved location not search in connection string. (user on different production databses is the same so mostly the dynamic part will be the Database only)

2)Is there anywayout to encrypt that password in configuration table entries.(Some users have access to the DB which holds the configuration table but they dont have access to production server)

if someone knows some other wayout to deal with this situation except the solution earlier provided.

Thanks and Regards

Manoj Kumar

sql

How to loop through in sql server 2000

I have a scenario where I have to keep track of users last three passwords,

if the new password entered by user matches with the last three passwords in the PasswordHistory Table then I will display message to user that "you can not repeat any of your last three passwords.."

Basically I can do : select top 3 passwords from MyTable order by createdDate desc

But how to do comparision ?

I was googling for if anything like array exist in sql server 2000?

Please suggest, any pointer......

Thanks

You can use the IN keyword to see if a scalar value is within a set of values.

Here's how I would do this. I'd have a stored procedure that accepts the @.NewPassword and @.UserID as input parameters and then returns either a 1 or a 0, and in .NET code I'd do an ExecuteScalar() and cast the result to a Boolean.

The T-SQL in the stored procedure would look something like:

IF @.NewPassword IN (SELECT TOP 3 Passwords FROM PasswordHistory WHERE UserID = @.UserID ORDER BY CreatedDate DESC)
SELECT 1
ELSE
SELECT 0

|||

Hi Scott,

Thanks for your reply, its surely something which i wanted , let me try it .

will let you know how it works out.

|||

It does work !!

Thanks a lot for your time.

|||

gridview:

It does work !!

Thanks a lot for your time.

Great! Happy Programming!

|||

gridview:

It does work !!

Thanks a lot for your time.

Great! Happy Programming!

How to login to SQL servers in a DTS?

Hello,

I have a DTS package in which I have several flows using different connections (OLE DB Providers, etc...) with Login ID and password.

But whenever the password changes, I need to manually update all the connections I have in this DTS. Isn't there an easier way to do that, somewhere via code or something else?
DTS or SSIS?

For DTS questions, see the DTS forum: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg|||

Assuming you're talking about SSIS, you can consider other package protection levels (avoid password encryption) - e.g. ServerStorage.

See http://technet.microsoft.com/en-us/library/ms141747(SQL.90).aspx

How to login to SQL servers in a DTS?

Hello,

I have a DTS package in which I have several flows using different connections (OLE DB Providers, etc...) with Login ID and password.

But whenever the password changes, I need to manually update all the connections I have in this DTS. Isn't there an easier way to do that, somewhere via code or something else?
DTS or SSIS?

For DTS questions, see the DTS forum: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg|||

Assuming you're talking about SSIS, you can consider other package protection levels (avoid password encryption) - e.g. ServerStorage.

See http://technet.microsoft.com/en-us/library/ms141747(SQL.90).aspx

How to load any file into FTP server through Javascript.

Dear all,
Iam with a small problem.
I want to access to FTP server through Html page by entering a username
and password.In the same html page I have to slect a file through
browse button and load it into FTP server.
PLz help me with the code also.
All this should be in Javascript.
Any help is appreciated...
Thanks a lot...~!~!~!
Bye
Hi
If this is ASP then you can use one of the products mentioned on
http://www.aspfaq.com/show.asp?id=2189.
This is not really an XML or SQL Server question so posting in a more
appropriate group may be an idea!
John
"vinodh" <vinodh.singh@.gmail.com> wrote in message
news:1126834578.588797.184610@.f14g2000cwb.googlegr oups.com...
> Dear all,
>
> Iam with a small problem.
> I want to access to FTP server through Html page by entering a username
> and password.In the same html page I have to slect a file through
> browse button and load it into FTP server.
> PLz help me with the code also.
> All this should be in Javascript.
> Any help is appreciated...
>
> Thanks a lot...~!~!~!
> Bye
>

How to load any file into FTP server through Javascript.

Dear all,
Iam with a small problem.
I want to access to FTP server through Html page by entering a username
and password.In the same html page I have to slect a file through
browse button and load it into FTP server.
PLz help me with the code also.
All this should be in Javascript.
Any help is appreciated...
Thanks a lot...~!~!~!
ByeHi
If this is ASP then you can use one of the products mentioned on
http://www.aspfaq.com/show.asp?id=2189.
This is not really an XML or SQL Server question so posting in a more
appropriate group may be an idea!
John
"vinodh" <vinodh.singh@.gmail.com> wrote in message
news:1126834578.588797.184610@.f14g2000cwb.googlegroups.com...
> Dear all,
>
> Iam with a small problem.
> I want to access to FTP server through Html page by entering a username
> and password.In the same html page I have to slect a file through
> browse button and load it into FTP server.
> PLz help me with the code also.
> All this should be in Javascript.
> Any help is appreciated...
>
> Thanks a lot...~!~!~!
> Bye
>