Passwords retrievable via SQLDMO

I was startled, while experimenting with the SQLDMO library, to find out
that it allows you to see the passwords that were used to register SQL
Servers in Enterprise Manager. Is it me, or is this an astonishing security
breach?
Harlan Messinger
Remove the first dot from my e-mail address.
Veuillez ter le premier point de mon adresse de courriel.Correct, but you already knew the password, since this is a per user
registration, by default this information is stored in the HKEY_CURRENT_USER
hive in the Registry, so only the actual user who made the registration can
read the password he used himself to register, so he already knew the
password to begin with, you are not exposing more information in that case.
And since it is in the HEKY_CURREN_USER you need to login in with the NT
credentials of the user who created the entry to access it.
Besides that, this is why there is an option that says "Always prompt for
login name and password" which is what you should use in my opinion if you
are using standard security. Or you can always use integrated security, in
which case you do not have this problem.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Harlan Messinger" <h.messinger@.comcast.net> wrote in message
news:34gdfoF4afco0U1@.individual.net...
>I was startled, while experimenting with the SQLDMO library, to find out
> that it allows you to see the passwords that were used to register SQL
> Servers in Enterprise Manager. Is it me, or is this an astonishing
> security
> breach?
> --
> Harlan Messinger
> Remove the first dot from my e-mail address.
> Veuillez ter le premier point de mon adresse de courriel.
>

Passwords not case sensitive?

I have a server that uses specific login/passwords. And through random
testing i noticed that the passwords are not case sensitive. Can this be
changed so that the passwords ARE case sensitive?
thanks,
BenHi,
For a Case insensitive sort order/carecter set it is not possible. If you
have a Case sensitive sortorder/char set then automatically
passwords will be case sensitive.
Thanks
Hari
SQL Server MVP
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:0E632896-37E7-4EA0-8022-5AC2D4230F84@.microsoft.com...
>I have a server that uses specific login/passwords. And through random
> testing i noticed that the passwords are not case sensitive. Can this be
> changed so that the passwords ARE case sensitive?
> thanks,
> Ben|||SQL Server 2000 maintains two versions of each login password. One is the
actual password supplied by the user, and the other is the password
converted by to all uppercase letters. This enables case-insensitive
validation of passwords (for case insensitive collations). Although this
behavior is convenient for many users, it makes password-guessing attacks
easier by reducing the number of possible passwords
In SQL 2005 only the actual password is stored. A password entered by a user
must match the password stored in the server (regardless of collation,
however the username can still be in a different case). If a password does
not match the password stored in SQL Server, the login fails. If the precise
case of the password characters is forgotten, the password must be reset.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:0E632896-37E7-4EA0-8022-5AC2D4230F84@.microsoft.com...
>I have a server that uses specific login/passwords. And through random
> testing i noticed that the passwords are not case sensitive. Can this be
> changed so that the passwords ARE case sensitive?
> thanks,
> Ben|||Hi,
Actually it because of *Collation setting* look for your collation and
change it accordingly to case sensitive from case insensitive .
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Ben" wrote:

> I have a server that uses specific login/passwords. And through random
> testing i noticed that the passwords are not case sensitive. Can this be
> changed so that the passwords ARE case sensitive?
> thanks,
> Ben

Passwords in the SSIS configuration file

Thanks to some earlier help from this forum, I have my package running using configuration files and Windows Authentication. However, when I try to execute the package using Sql authentication, login fails for the user i am specifying. When I check out the configuration file, the connection string does not contain the password. I added the password to the connection string, but it does not cone back. I followed the instructions found on the following site:

http://www.nigelrivett.net/DTS/SSISConfigurationFile.html

After trying what was suggested there, I was still unsuccessful with my login attempt.

Anyone know how to set up a package to run using Sql authentication?

Thanks!

Craig

At the time of development, set the protection level of package to EncryptSensitiveWithUserKey, after this sertting the package will store the sensitive information like password in itself.

Atul

Passwords in Sql Server 2000

HI, How to save passwords in sql server server 2000, i mean i build
database that consist table of user , in this table i want to storage
user's details including their passwords. As i understand it's not good
idea to storage password like other data i mean name , last name.
How to accomplish this?

--
Message posted via http://www.sqlmonster.comYou would usually handle the encryption/decrpytion in your application,
using an API like the Win32 CryptoAPI or the .NET libraries:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22

Simon

Passwords for the logins

Hi All,
Sql server 7
There are set of logins in sql server some are NT authentication and some are sql server authentication. How can i retrieve the passwords for both these logins.
TIA
AdilYou must be kind of foggy on the concept. They are passwords. They are supposed to be secret, and SQL Server keeps them that way.

If you have sufficient rights you can reset the passwords to whatever you want.

Passwords for the logins

Hi All,
Sql server 7
There are set of logins in sql server some are NT
authentication and some are sql server
authentication. How can i retrieve the passwords for both
these logins.
TIA
AdilHaseeb,
the nearest you can do is to extract the password in encrypted format for
SQL logins (only):
SELECT CONVERT(VARBINARY(32), password)
FROM syslogins
WHERE name = 'Margaret'
This can be useful to transfer logins from one server to another.
HTH,
Paul Ibison|||Hi,
You can not retrive the passwords of users in clear text. Only way is to
change the password.
For OS level. You have to change in OS level users password.
For SQL Srever, use enterprise manager or sp_password command from query
analyzer to change the password.
Thanks
Hari
MCDBA
"haseeb" <anonymous@.discussions.microsoft.com> wrote in message
news:16a3b01c447f9$9a5e0be0$a101280a@.phx
.gbl...
> Hi All,
> Sql server 7
> There are set of logins in sql server some are NT
> authentication and some are sql server
> authentication. How can i retrieve the passwords for both
> these logins.
>
> TIA
> Adil|||You can't.
The NT logins are the domain user accounts.
The SQL logins are ones that were created by an sql admin. Probably used by
an specific application. You could back track to the app and get the
password.
"haseeb" <anonymous@.discussions.microsoft.com> wrote in message
news:16a3b01c447f9$9a5e0be0$a101280a@.phx
.gbl...
> Hi All,
> Sql server 7
> There are set of logins in sql server some are NT
> authentication and some are sql server
> authentication. How can i retrieve the passwords for both
> these logins.
>
> TIA
> Adil

Passwords for cluster and servers

I have a 2 node cluster running Win2000 enterprise and MS clustering.
Also SQL server. Would there be a problem using the same domain
password for SQL and for the clustering service? I thought I read
that there may be a problem but I don't recall where it read it.
Thanks for any help.
I'm using one domain account for SQL and Cluster services and I have no
problems.
Ayad Shammout
SR.DBA/Analyst
Caregroup IS
"Dave" <mine@.none.com> wrote in message
news:foam11pg95kfd57jefn88eou8o160kc3r1@.4ax.com...
> I have a 2 node cluster running Win2000 enterprise and MS clustering.
> Also SQL server. Would there be a problem using the same domain
> password for SQL and for the clustering service? I thought I read
> that there may be a problem but I don't recall where it read it.
> Thanks for any help.
>
|||im using 1 account for cluster and sql services, havent had any issues to
date...
"Dave" <mine@.none.com> wrote in message
news:foam11pg95kfd57jefn88eou8o160kc3r1@.4ax.com...
> I have a 2 node cluster running Win2000 enterprise and MS clustering.
> Also SQL server. Would there be a problem using the same domain
> password for SQL and for the clustering service? I thought I read
> that there may be a problem but I don't recall where it read it.
> Thanks for any help.
>
|||I don't think there's a functional issue, but if you use the same account,
the domain admins will be able to log on to SQL Server as an SA. If you use
separate accounts, you can set the cluster account to have minimal SQL
privledges so the keep alive for SQL will work and not give the domain
admins sa access to the database. In a small environment using the same
account is probably no big deal, but in a larger enterprise it could be.
"Dave" <mine@.none.com> wrote in message
news:foam11pg95kfd57jefn88eou8o160kc3r1@.4ax.com...
> I have a 2 node cluster running Win2000 enterprise and MS clustering.
> Also SQL server. Would there be a problem using the same domain
> password for SQL and for the clustering service? I thought I read
> that there may be a problem but I don't recall where it read it.
> Thanks for any help.
>
|||If you do not want the domain admins to be admins of sql then remove
buildin/admin group, create your own group "Sql Admins" add users who need
admin to sql and add that.
apart for that , using same cluster and sql login is not a prob
"Al" <al@.nospam.com> wrote in message
news:OBnYmUVGFHA.1836@.tk2msftngp13.phx.gbl...
> I don't think there's a functional issue, but if you use the same account,
> the domain admins will be able to log on to SQL Server as an SA. If you
use
> separate accounts, you can set the cluster account to have minimal SQL
> privledges so the keep alive for SQL will work and not give the domain
> admins sa access to the database. In a small environment using the same
> account is probably no big deal, but in a larger enterprise it could be.
> "Dave" <mine@.none.com> wrote in message
> news:foam11pg95kfd57jefn88eou8o160kc3r1@.4ax.com...
>

Password's case sensitivity

Hi,
somehow my Sql Server's passwors are not case sensitive. I thought sql
server's login passwords are case sensitive.
Am I worng?
How do I change my SQL Server back to the case sensitive passwords?
Please help..
Thank you
SamHi
Passwords case sensitivity is based on the collation of the installation. If
you install with a non-case sensitive collation, the password will not be
cases sensitive.
Regards
Mike
"sam" wrote:

> Hi,
> somehow my Sql Server's passwors are not case sensitive. I thought sql
> server's login passwords are case sensitive.
> Am I worng?
> How do I change my SQL Server back to the case sensitive passwords?
> Please help..
> Thank you
> Sam|||Password case-sensitivity is determined by the default collation for the SQL
Server instance. It appears you have a case-insensitive collation.
Hope this helps.
Dan Guzman
SQL Server MVP
"sam" <sam@.discussions.microsoft.com> wrote in message
news:BDB6FA4B-D047-438F-A850-0E3FD0A8F49B@.microsoft.com...
> Hi,
> somehow my Sql Server's passwors are not case sensitive. I thought sql
> server's login passwords are case sensitive.
> Am I worng?
> How do I change my SQL Server back to the case sensitive passwords?
> Please help..
> Thank you
> Sam|||Thank you for your response. I think the Earlier I used all the SQL servers
which are upgraded from 7.0 to 2K and so the defautl collation of 7.0 was a
case sensitive. (Not usre right now).
But if I want to keep all my client data and other code to be case
-insensitive but all my login passwords to be case sensitive , can I do that
?
How can I do that?
Please let me know..
Thank you again..
Sam
"Dan Guzman" wrote:

> Password case-sensitivity is determined by the default collation for the S
QL
> Server instance. It appears you have a case-insensitive collation.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sam" <sam@.discussions.microsoft.com> wrote in message
> news:BDB6FA4B-D047-438F-A850-0E3FD0A8F49B@.microsoft.com...
>
>|||To change the instance default collation after installation, you'll need to
either reinstall or rebuild the master database. This will create new
master, msdb and model databases.
Although you can run with mixed collations, this can lead to problems. The
tempdb collation will be the instance default so you might get errors due to
collation conflicts when using temp tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"sam" <sam@.discussions.microsoft.com> wrote in message
news:96622B87-A747-4E23-8593-27221C2C10BD@.microsoft.com...[vbcol=seagreen]
> Thank you for your response. I think the Earlier I used all the SQL
> servers
> which are upgraded from 7.0 to 2K and so the defautl collation of 7.0 was
> a
> case sensitive. (Not usre right now).
> But if I want to keep all my client data and other code to be case
> -insensitive but all my login passwords to be case sensitive , can I do
> that?
> How can I do that?
> Please let me know..
> Thank you again..
> Sam
> "Dan Guzman" wrote:
>|||Thanks Dan,
But I think I did not put my question clearly. My Apologies.
I want to keep the collation of all the DBs on the server same as what I
have at present (case in-sensitive) but only all my login passwords should b
e
case sensitive.
Can I do that?
How?
Thank you
Sam
"Dan Guzman" wrote:

> To change the instance default collation after installation, you'll need t
o
> either reinstall or rebuild the master database. This will create new
> master, msdb and model databases.
> Although you can run with mixed collations, this can lead to problems. Th
e
> tempdb collation will be the instance default so you might get errors due
to
> collation conflicts when using temp tables.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sam" <sam@.discussions.microsoft.com> wrote in message
> news:96622B87-A747-4E23-8593-27221C2C10BD@.microsoft.com...
>
>|||Running mixed collations is the only way to have case-sensitive passwords
and case-insensitive user database collations. The default collation of
your existing databases will not be changed after you reattach following a
master database rebuild or SQL Server reinstall.
However, as I mentioned in my previous response, mixed collations will have
other consequences. If you feel compelled to try this, be sure to
thoroughly test your applications before implementing this in production.
Hope this helps.
Dan Guzman
SQL Server MVP
"sam" <sam@.discussions.microsoft.com> wrote in message
news:B4EB814F-DCAD-4AF1-AB66-11C821BFD09D@.microsoft.com...[vbcol=seagreen]
> Thanks Dan,
> But I think I did not put my question clearly. My Apologies.
> I want to keep the collation of all the DBs on the server same as what I
> have at present (case in-sensitive) but only all my login passwords should
> be
> case sensitive.
> Can I do that?
> How?
> Thank you
> Sam
> "Dan Guzman" wrote:
>|||I understand your concern and thank you for your response.
Bu insterad of rebuilding my master database, Can I change the collation of
the password column of the sysxlogin table?
This is just a guess. I think you must have thought of this but I still want
to ask you this question.
"Dan Guzman" wrote:

> Running mixed collations is the only way to have case-sensitive passwords
> and case-insensitive user database collations. The default collation of
> your existing databases will not be changed after you reattach following a
> master database rebuild or SQL Server reinstall.
> However, as I mentioned in my previous response, mixed collations will hav
e
> other consequences. If you feel compelled to try this, be sure to
> thoroughly test your applications before implementing this in production.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sam" <sam@.discussions.microsoft.com> wrote in message
> news:B4EB814F-DCAD-4AF1-AB66-11C821BFD09D@.microsoft.com...
>
>|||> Bu insterad of rebuilding my master database, Can I change the collation
> of
> the password column of the sysxlogin table?
Changing system table schema is not supported so you would this is at your
own risk. The supported method is a mixed collation environment with a
case-sensitive instance default collation and case-insensitive collation for
user databases. Of course, mixed collation caveats apply.
Hope this helps.
Dan Guzman
SQL Server MVP
"sam" <sam@.discussions.microsoft.com> wrote in message
news:D516F2B3-79BF-414E-B005-88AB683F5DD5@.microsoft.com...[vbcol=seagreen]
>I understand your concern and thank you for your response.
> Bu insterad of rebuilding my master database, Can I change the collation
> of
> the password column of the sysxlogin table?
> This is just a guess. I think you must have thought of this but I still
> want
> to ask you this question.
> "Dan Guzman" wrote:
>|||Thank you Dan,
I will try this in the development environment and then see how it works
before changing the collation for any thing else.
Thank you once again for your time and response.
Sam
"Dan Guzman" wrote:

> Changing system table schema is not supported so you would this is at your
> own risk. The supported method is a mixed collation environment with a
> case-sensitive instance default collation and case-insensitive collation f
or
> user databases. Of course, mixed collation caveats apply.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sam" <sam@.discussions.microsoft.com> wrote in message
> news:D516F2B3-79BF-414E-B005-88AB683F5DD5@.microsoft.com...
>
>

passwords are case sensitive in 2005

Can we change the SQL passwords in 2005 to be case insensitive ?
ThanksNo...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.hotmail.com> wrote in message news:elz9A0Q6HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Can we change the SQL passwords in 2005 to be case insensitive ?
> Thanks
>

Passwords

How would i set a Database password that NOONE could see the Database layouts or field names and Data....i mean obviously there are hackers that can get programs to "Bust" into Most database but in general how would i prevent someone from looking at the layout and information?

My next question is a tough one, I THINK......I have a front end of VB6,......i want the back end to be SQL Server or SQL Sever Express 2005 When someone goes to login into my software product i would like to check a DB of valid User Names and Passwords... Now if I SQL Server running on TRUE Server, ie Win 2003 Server....could 50 people login at the same time to my software from 50 different PC accross the network? Is that what SQL Server means by Connections? or could everyone use the "sa" login?

My application handles all the locking and unlocking of databases and records in the DB...so i dont have to rely on SQL Server for that....essentionally SQL Server is a Data DUmp for me.......what does everyone think?

Also.....can you re-distribute the MDF files aka..Databases....like you would Access without having SQL Server installed....? If your front end is VB6 and the MDF will that work? b/c the software validates its login with the list in the DB not what SQL Considers a Login....Just like you can take a Access DB named "Test.MDB" rename it "Test.xxx" and re-distribute that wo whoever you want and they dont need the Access runtime to operate the VB6 software....obviously Access uses JET ...what does SQL Server Use.....couldnt you do the same thing with SQL Server?

Thanks All!!!

moving the thread to the database engine forum.|||

If you distribute an application and database anyone having the binaries of those will be able to hack your application. Obfuscating might delay hacking.

What I have understood you will need SQL 2005 Express to be installed to attach mdf files.

According to documentation there is no limit for connections, you will need to configure it to allow remote connections though.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

I would use SQL servers ability to lock/unlock data rather than program it myself.

|||hi . did u get the answer?I have same problem . please send me if u find
|||

dragonsbb16 wrote:

1 How would i set a Database password that NOONE could see the Database layouts or field names and Data....i mean obviously there are hackers that can get programs to "Bust" into Most database but in general how would i prevent someone from looking at the layout and information?

2 My next question is a tough one, I THINK......I have a front end of VB6,......i want the back end to be SQL Server or SQL Sever Express 2005 When someone goes to login into my software product i would like to check a DB of valid User Names and Passwords... Now if I SQL Server running on TRUE Server, ie Win 2003 Server....could 50 people login at the same time to my software from 50 different PC accross the network? Is that what SQL Server means by Connections? or could everyone use the "sa" login?

My application handles all the locking and unlocking of databases and records in the DB...so i dont have to rely on SQL Server for that....essentionally SQL Server is a Data DUmp for me.......what does everyone think?

Also.....can you re-distribute the MDF files aka..Databases....like you would Access without having SQL Server installed....? If your front end is VB6 and the MDF will that work? b/c the software validates its login with the list in the DB not what SQL Considers a Login....Just like you can take a Access DB named "Test.MDB" rename it "Test.xxx" and re-distribute that wo whoever you want and they dont need the Access runtime to operate the VB6 software....obviously Access uses JET ...what does SQL Server Use.....couldnt you do the same thing with SQL Server?

Thanks All!!!

Edit : CONDENSED ANSWER - Lost long post due Session Timeout :(

1. You can grant to right to accounts to "view definitions" to various objects. Check "Permissions" in BOL

2. You can use ONE Account to Acces the DB and then check inside the DB if there is a User created for this person. This is used for most Forums for example. Security Tips : DONT give SA Accounts to everyone. 2. Storing Passwords in SQL Server you should take a look at Hashing (and Salting them)

3. Can be done. Ask yourself the important question : How likely is it that someone will mess with my data while i am messing with it and how long will it take to mess with it? If the answer is even the slightest "could be" then you need to think about locking. Either way. You will allways "piss off" one guy.. So you take the pick which one. Also for some applications locking is harder to implement (for example web access with connections that are closed between page calls). See Timestamp fields. and optimistic and pessimistic locking

4. To open a SQL 2005 mdf file you need any Version of SQL 2005. But express is free and can be downloaded. Check the installation instructions for command line installations if you wish to include it in a setup package.

Password Feild as passwordchar

Hi,

I want to put passwords in my sql table not in clear text but in password char. Pls help me

Hi, you can do it by encrypting the password text and then storing in database. Encryption can be done in asp.net and also in database (if DB engine support encryption e.g. in oracle)

|||

Thanks It works

Password expired

Hello!
My users SQL passwords expires after 90 days and that's what I want.
The problem is that I want the user to be able to change their password when
this happends.
I developed a small application for this purpose, but I can't get it to
work.
There are an asp.net page with five textboxes: server, loginname,
oldpassword, newpassword, and confirmnewpassword.
First I'm trying to establish a sqlconnection with the server, loginname and
the oldpassword, but the row conn.open(); raises an error with: Reason: The
password of the account has expired
Well, I can understand why, but I don't understand how to solve this
solution.
The next row where ment to be ALTER LOGIN thename WITH PASSWORD = 'blabla2'
OLD_PASSWORD = 'blabla1', and this works when able to login, but in this
case I can't establish the connection.
How to solve this
Best Regards /MagnusMagnus,
To achieve this you should be using windows integrated authentication rather
than SQL, this would be the simplest way.
You could do this in SQL authentication as well but bit complicated, what
you should do is, your password changing application should use a dedicated
sql use account, that login should have all db_admin right. Use that login
to logon to sql server to change the password
Regards
VT
Knowledge is power, share it
http://oneplace4sql.blogspot.com/
"Magnus" <magnus.blomberg@.skanska.se> wrote in message
news:uNBOhw4lHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hello!
> My users SQL passwords expires after 90 days and that's what I want.
> The problem is that I want the user to be able to change their password
> when this happends.
> I developed a small application for this purpose, but I can't get it to
> work.
> There are an asp.net page with five textboxes: server, loginname,
> oldpassword, newpassword, and confirmnewpassword.
> First I'm trying to establish a sqlconnection with the server, loginname
> and the oldpassword, but the row conn.open(); raises an error with:
> Reason: The password of the account has expired
> Well, I can understand why, but I don't understand how to solve this
> solution.
> The next row where ment to be ALTER LOGIN thename WITH PASSWORD =
> 'blabla2' OLD_PASSWORD = 'blabla1', and this works when able to login, but
> in this case I can't establish the connection.
> How to solve this
> Best Regards /Magnus
>
>|||Hi!
Thanks. Well, I know Windows Authentication is the prefered way, but in this
case, not!
Okay, so I setup an account that has access to change the password then. So
it's not possible to use the existing login to change the password? That
mean that password expired has the same impact as account locked out or
account disabled in this case?
Should I use sa or a dedicated?
Regards Magnus
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:OqDR2S5lHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Magnus,
> To achieve this you should be using windows integrated authentication
> rather than SQL, this would be the simplest way.
> You could do this in SQL authentication as well but bit complicated, what
> you should do is, your password changing application should use a
> dedicated sql use account, that login should have all db_admin right. Use
> that login to logon to sql server to change the password
>
> Regards
> VT
> Knowledge is power, share it
> http://oneplace4sql.blogspot.com/
>
>
>
>
> "Magnus" <magnus.blomberg@.skanska.se> wrote in message
> news:uNBOhw4lHHA.588@.TK2MSFTNGP06.phx.gbl...
>|||Personally I will not use sa, I would rather create a login that's only for
this purpose
Regards
VT
Knowledge is power, share it
http://oneplace4sql.blogspot.com/
"Magnus" <magnus.blomberg@.skanska.se> wrote in message
news:%23$hIvg5lHHA.4592@.TK2MSFTNGP05.phx.gbl...
> Hi!
> Thanks. Well, I know Windows Authentication is the prefered way, but in
> this case, not!
> Okay, so I setup an account that has access to change the password then.
> So it's not possible to use the existing login to change the password?
> That mean that password expired has the same impact as account locked out
> or account disabled in this case?
> Should I use sa or a dedicated?
> Regards Magnus
>
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:OqDR2S5lHHA.1216@.TK2MSFTNGP03.phx.gbl...
>|||A user can change their own password as long as they change it before it
expires. If you let them change it after it expires, there would be no
point in having it expire would there? Once it expires, the dba will have
to change it for them. I suggest telling them to put a reminder in their
calendar to change the password a week or so before it expires.
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
"Magnus" <magnus.blomberg@.skanska.se> wrote in message
news:uNBOhw4lHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hello!
> My users SQL passwords expires after 90 days and that's what I want.
> The problem is that I want the user to be able to change their password
> when this happends.
> I developed a small application for this purpose, but I can't get it to
> work.
> There are an asp.net page with five textboxes: server, loginname,
> oldpassword, newpassword, and confirmnewpassword.
> First I'm trying to establish a sqlconnection with the server, loginname
> and the oldpassword, but the row conn.open(); raises an error with:
> Reason: The password of the account has expired
> Well, I can understand why, but I don't understand how to solve this
> solution.
> The next row where ment to be ALTER LOGIN thename WITH PASSWORD =
> 'blabla2' OLD_PASSWORD = 'blabla1', and this works when able to login, but
> in this case I can't establish the connection.
> How to solve this
> Best Regards /Magnus
>
>|||[posted and mailed]
Magnus (magnus.blomberg@.skanska.se) writes:
> My users SQL passwords expires after 90 days and that's what I want. The
> problem is that I want the user to be able to change their password when
> this happends.
> I developed a small application for this purpose, but I can't get it to
> work.
> There are an asp.net page with five textboxes: server, loginname,
> oldpassword, newpassword, and confirmnewpassword.
Despite what Roger and vt says, this is indeed possible. You simply connect
to SQL Server and in the connect string you pass the new password in the
Password property. The old password you pass in the Old Password property.
For instance:
PROVIDER=SQLNCLI;Server=ThatServer;Datab
ase=ThatDatabase;UID=ThisUser;
Password=SprillansNytt;Old Password=Frrarets
Note that you must use the SQLNCLI provider, as SQLOLEDB does not support
the Old Password property.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||So passwords never really expire? Cool!
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
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99339BA797939Yazorman@.127.0.0.1...
> [posted and mailed]
> Magnus (magnus.blomberg@.skanska.se) writes:
> Despite what Roger and vt says, this is indeed possible. You simply
> connect
> to SQL Server and in the connect string you pass the new password in the
> Password property. The old password you pass in the Old Password property.
> For instance:
> PROVIDER=SQLNCLI;Server=ThatServer;Datab
ase=ThatDatabase;UID=ThisUser;
> Password=SprillansNytt;Old Password=Frrarets
> Note that you must use the SQLNCLI provider, as SQLOLEDB does not support
> the Old Password property.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland!
I thougth it should be a way, since this is the same behavior as for the AD
accounts. When it has expired, you have to change the password, but you can
still do it!
Regarding to the fact that SQLOLEDB provider doesn't support this, how to
change the code below to work?
String strconn = "PROVIDER=SQLNCLI;Server=" + TextBox1.Text +
";Database=master;UID=" + TextBox2.Text +
";Password=" + TextBox4.Text + ";Old Password=" + TextBox3.Text;
System.Data.SqlClient.SqlConnection conn = new
System.Data.SqlClient.SqlConnection(strconn);
Is it OK to use this kind of code? Injection must be impossible/useless to
use within the connection string. Isn't it?
Best regards
/Magnus
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99339BA797939Yazorman@.127.0.0.1...
> [posted and mailed]
> Magnus (magnus.blomberg@.skanska.se) writes:
> Despite what Roger and vt says, this is indeed possible. You simply
> connect
> to SQL Server and in the connect string you pass the new password in the
> Password property. The old password you pass in the Old Password property.
> For instance:
> PROVIDER=SQLNCLI;Server=ThatServer;Datab
ase=ThatDatabase;UID=ThisUser;
> Password=SprillansNytt;Old Password=Frrarets
> Note that you must use the SQLNCLI provider, as SQLOLEDB does not support
> the Old Password property.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||At the ...conn = new... row I get the error message Keyword not supported:
'provider'.
/Magnus
"Magnus" <magnus.blomberg@.skanska.se> wrote in message
news:uLGHYm5mHHA.1220@.TK2MSFTNGP03.phx.gbl...
> Thanks Erland!
> I thougth it should be a way, since this is the same behavior as for the
> AD accounts. When it has expired, you have to change the password, but you
> can still do it!
> Regarding to the fact that SQLOLEDB provider doesn't support this, how to
> change the code below to work?
> String strconn = "PROVIDER=SQLNCLI;Server=" + TextBox1.Text +
> ";Database=master;UID=" + TextBox2.Text +
> ";Password=" + TextBox4.Text + ";Old Password=" +
> TextBox3.Text;
> System.Data.SqlClient.SqlConnection conn = new
> System.Data.SqlClient.SqlConnection(strconn);
> Is it OK to use this kind of code? Injection must be impossible/useless to
> use within the connection string. Isn't it?
> Best regards
> /Magnus
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns99339BA797939Yazorman@.127.0.0.1...
>|||Magnus (magnus.blomberg@.skanska.se) writes:
> I thougth it should be a way, since this is the same behavior as for the
> AD accounts. When it has expired, you have to change the password, but
> you can still do it!
> Regarding to the fact that SQLOLEDB provider doesn't support this, how to
> change the code below to work?
> String strconn = "PROVIDER=SQLNCLI;Server=" + TextBox1.Text +
> ";Database=master;UID=" + TextBox2.Text +
> ";Password=" + TextBox4.Text + ";Old Password=" + TextBox3.Text;
> System.Data.SqlClient.SqlConnection conn = new
> System.Data.SqlClient.SqlConnection(strconn);
> Is it OK to use this kind of code? Injection must be impossible/useless to
> use within the connection string. Isn't it?
Impossible it is not. I mean if the user think you should run with
MARS enabled, he can arrange it. More importantly, the user may want to
use a semicolon in his password.

> At the ...conn = new... row I get the error message Keyword not supported:
> 'provider'.
I XXX that you use ASP .Net, so I guess that you are using SqlClient as
your API. (Well, you should.) I looked at SQL Native Client, since this
is where I've played with this feature. Just remove the Provider part.
Not that I know, but I guess the connection-string properties are the
same with SqlClient.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

password encryption

I have an Access .adp application and I need to store the passwords encrypted. I see that there are unsupported functions pwdencrypt() and pwdcompare() in SQL server 2K.
Are there any other functions or methods in SQL Server 2000 that could be called that would provide this capability?Are there any other functions or methods in SQL Server 2000 that could be called that would provide this capability?

SQL Server 2000 encryption is pretty weak. 2005 is supposed to address this. In the meantime, you might consider this link:

http://www.sqlservercentral.com/columnists/mcoles/freeencryption.asp

(you may have to register, but registration is free).

Alternatively, you could use the Micrsoft Crypto API (capicom.dll) or the .Net Encryption utilities (don't have more specific references on these).

Regards,

hmscott

Password Data Types

I have recently switched my company's database over to MSS2k and all has been going fine untill i came along passwords. I was wondering if there is a password data type like there is in access (where it only shows ****** instead of PASSWORD). I was also wondering if there is a crypt command like in PHP and MySQL or am I stuck writing my own vba for that one.You have to write your own vba|||Here is a one-way encryption algorythm I wrote. You are free to use, just leave my credit in the header!

Store the encrypted password in a table, and noone will be able to unencrypt it. When someone supplies logs in, encrypt the password they supply to see if it matches the one in their user record.

blindman

----------
CREATE FUNCTION [dbo].[Encrypt_Password]
(@.RawPassword varchar(20))
Returns varchar(20)
as
BEGIN
--Function dbo.EncryptPassword
--Bruce Lindman, 11/19/2002
--
--This function returns a 20 character encryption string derived from a supplied password.
--It uses a non-linear deterministic number generation algorithm known as the Linear Congruential Method
--to generate pseudo-random numbers from the Ascii values of the password characters, and these
--random numbers are then converted back into Ascii characters to form the the encrypted string.
--Because the algorithm is non-linear and uses the password itself as the initial key value, it should be
--practically impossible to reverse engineer the process.

--These variables used for testing
--declare @.RawPassword varchar(20)
--set @.RawPassword = 'Pa$$w0rD'
--set @.RawPassword = '!!!!!!!!!!' --A low ascii value password
--set @.RawPassword = '' --A high ascii value password

declare @.counter int --we'll use this to step through the password character by character
declare @.seed decimal(10, 9) --The derived seed value for the random number generator
declare @.EncryptedPassword varchar(20)
set @.EncryptedPassword = ''
declare @.Modulo int --The divisor in the random number generator
set @.Modulo = 100000000
declare @.Multiplier int --The multiplier in the random number generator
declare @.AsciiValue numeric

--Extend the password to 20 characters by repeating it, separated by the character x
--The x character ensures that password ABC does not return the same value when doubled,
--as ABCABC, but passwords that are doubled with a padded x character will return the same
--encrypted value. ABC returns the same value as ABCxABC or ABCxABCxABC.
while datalength(@.RawPassword) < 20
begin
set @.RawPassword = @.RawPassword + 'x' + @.RawPassword
end
--I think it is unavoidable that for any function F() there exists a pair of values A, B such
--that F(A) = F(B).

--Derive the seed value for the random number function from the password itself
set @.counter = 0
set @.seed = 1
while @.counter < datalength(@.RawPassword)
begin
set @.counter = @.counter + 1
--Use the ascii value of each character to revise the seed value
set @.AsciiValue = ascii(substring(@.RawPassword, @.Counter, 1))
set @.seed = @.seed * (@.AsciiValue/1000)
--We don't want any leading zeros in our decimal value, or the seed may get too small
while @.seed < 0.1 set @.seed = @.seed * 10
end

--We'll derive the multiplier from the seed value, following the principle that a good multiplier
--should be 1 digit less than the Modulo, and should follow the pattern ...x21 where x is an even number
set @.Multiplier = round(@.seed * @.Modulo/100, 0) * 200 + 21

--Now encrypt the password
set @.counter = 0
while @.counter < datalength(@.RawPassword)
begin
set @.counter = @.counter + 1
set @.AsciiValue = ascii(substring(@.RawPassword, @.Counter, 1))
--This next statement is the guts of the random number generator
--It creates a new seed value between 0 and 1
set @.seed = cast(cast(1 + (@.seed + @.AsciiValue/1000) * @.Multiplier * @.Modulo as bigint) % @.Modulo as numeric)/@.Modulo
--Now use the first three digits of the seed value to lookup an ascii character between 1 and 255 and append it to the encrypted password
set @.EncryptedPassword = @.EncryptedPassword + char(1 + cast(round(@.seed * 1000, 0) as int) % 254)
end

Return @.EncryptedPassword
end|||This is a code snippet, I found somewhere, using one of those SQL Server un-documented password functions.

DECLARE @.ClearPIN varchar(255)
DECLARE @.EncryptedPIN varbinary(255)
SELECT @.ClearPIN = 'test'
SELECT @.EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@.ClearPIN))
SELECT @.EncryptedPIN
SELECT pwdcompare(@.ClearPIN, @.EncryptedPIN, 0)

But I would always suggest you use your own encryption routines like the one Blindman wrote.

Blindman- Thanks for sharing your code. If I ever use it, I will definitely leave the header in there.|||There are a few problems with pwdencrypt:

It is an undocumented function, so that it is not guaranteed to work the same, or at all, in future realeases. In fact, its functionality changed between version 6.5 and 7.0 and it was no longer able to recognize old passwords.

The pwdencrypt function has suffered from a Buffer Overflow vulnerability in the past. I'm not sure if this had been corrected in current patches.

The pwdencrypt function is not very secure, and has been cracked:
http://www.nextgenss.com/papers/cracking-sql-passwords.pdf

Most of the research I did recommended writing customer code rather than using pwdencrypt, so that's what I did! I have used the function I posted in several applications now and have never had an issue with it.

blindman

Password change

Hi,
How do I set up sercurity enforcement to have users change their sql
passwords every 30 days?
Thanksmecn,
SQL Server adopts its policies from the Windows local policies. So, I
believe that you would have to change those.
(But, if your servers are having their policies synchronized with the domain
policies, I don't know if your changes will stick locally.)
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OdDICuIyHHA.5888@.TK2MSFTNGP02.phx.gbl...
> Hi,
> How do I set up sercurity enforcement to have users change their sql
> passwords every 30 days?
> Thanks
>|||In 2005 you do it in the CREATE LOGIN 8or change with ALTER LOGIN) command. In 2000, you can't do
that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:OdDICuIyHHA.5888@.TK2MSFTNGP02.phx.gbl...
> Hi,
> How do I set up sercurity enforcement to have users change their sql
> passwords every 30 days?
> Thanks
>|||My sql2005 is sql authentication. so the users are not windows based users
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%234wTN0IyHHA.4392@.TK2MSFTNGP04.phx.gbl...
> mecn,
> SQL Server adopts its policies from the Windows local policies. So, I
> believe that you would have to change those.
> (But, if your servers are having their policies synchronized with the
> domain policies, I don't know if your changes will stick locally.)
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:OdDICuIyHHA.5888@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> How do I set up sercurity enforcement to have users change their sql
>> passwords every 30 days?
>> Thanks
>>
>|||Thanks all
let me say it clearly, I need to enforce sql 2005 sql (authentication) users
to change their passwords every 30 days.
Is there a setting or check box that i could fill?
I don't know how.
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:F6BD45B2-27C2-4E6A-924E-FF9DDA37B970@.microsoft.com...
> In 2005 you do it in the CREATE LOGIN 8or change with ALTER LOGIN)
> command. In 2000, you can't do that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:OdDICuIyHHA.5888@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> How do I set up sercurity enforcement to have users change their sql
>> passwords every 30 days?
>> Thanks
>>
>|||Yes, I know. For SQL Server logins, SQL Server adopts the policies from the
Windows local server that is running the SQL Server. (Windows does its own
managing of Windows login policies.)
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uvKX38IyHHA.1208@.TK2MSFTNGP03.phx.gbl...
> My sql2005 is sql authentication. so the users are not windows based users
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%234wTN0IyHHA.4392@.TK2MSFTNGP04.phx.gbl...
>> mecn,
>> SQL Server adopts its policies from the Windows local policies. So, I
>> believe that you would have to change those.
>> (But, if your servers are having their policies synchronized with the
>> domain policies, I don't know if your changes will stick locally.)
>> RLF
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:OdDICuIyHHA.5888@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> How do I set up sercurity enforcement to have users change their sql
>> passwords every 30 days?
>> Thanks
>>
>>
>|||Did you read about the CREATE LOGIN command in Books Online? If you do, you will find a parameter
which specifies that the Windows policy password expiration policy should apply to this SQL Server
login.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:u978YDJyHHA.4928@.TK2MSFTNGP03.phx.gbl...
> Thanks all
> let me say it clearly, I need to enforce sql 2005 sql (authentication) users to change their
> passwords every 30 days.
> Is there a setting or check box that i could fill?
> I don't know how.
> Thanks
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:F6BD45B2-27C2-4E6A-924E-FF9DDA37B970@.microsoft.com...
>> In 2005 you do it in the CREATE LOGIN 8or change with ALTER LOGIN) command. In 2000, you can't do
>> that.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message news:OdDICuIyHHA.5888@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> How do I set up sercurity enforcement to have users change their sql
>> passwords every 30 days?
>> Thanks
>>
>

Password change

Some one has changed all the passwords on the machine (NT4) that runs our
SQL Server 2000 demo. Now the SQL Server service wont start.
I tried changing the password to the new one through Serives in control
panel but get the following error:
Could not start MSSQLServer Service on \\myserver
Error 2140: An internal Windows NT error occured
I have also tried to select Log On With System Account with the same effect.
I have also tried doing all of this thorugh the SQL Server Enterprise
Manager (edit registration properties) but I just get the following message:
Aconnection could not be established to OURSERVER
Reason SQL serevr does not exist or access denied.
Can any one help?
--
Cheers,
elzikoAnything in the SQL Server errorlog?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:%23DxXRCogDHA.2400@.TK2MSFTNGP11.phx.gbl...
> Some one has changed all the passwords on the machine (NT4) that runs our
> SQL Server 2000 demo. Now the SQL Server service wont start.
> I tried changing the password to the new one through Serives in control
> panel but get the following error:
> Could not start MSSQLServer Service on \\myserver
> Error 2140: An internal Windows NT error occured
> I have also tried to select Log On With System Account with the same effect.
> I have also tried doing all of this thorugh the SQL Server Enterprise
> Manager (edit registration properties) but I just get the following message:
> Aconnection could not be established to OURSERVER
> Reason SQL serevr does not exist or access denied.
> Can any one help?
> --
> Cheers,
> elziko
>|||Mmm, I dont know which log you are talkign about but I have found a log
folder with several errorlog file inside. And they do happen to say:
"SQL Server evaluation perios has expired."
OK, so my evaluation is up (should have thought of that)! But is this really
my problem? Surely I would get a message to that effect if the service could
even start or is this sort of message normal for an end of eval?
--
Cheers,
elziko|||All I remember when en eval is out is that you can't start the service. I had to go looking in the
eventlog for just such a message before I realized what the problem was.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:ewWGadogDHA.2408@.TK2MSFTNGP09.phx.gbl...
> Mmm, I dont know which log you are talkign about but I have found a log
> folder with several errorlog file inside. And they do happen to say:
> "SQL Server evaluation perios has expired."
> OK, so my evaluation is up (should have thought of that)! But is this really
> my problem? Surely I would get a message to that effect if the service could
> even start or is this sort of message normal for an end of eval?
> --
> Cheers,
> elziko
>