dba123
asked on
Problem with connection string
I have checked out my sql login twice. I get an error when I debug my ASP.NET page telling me my login for sqlapps failed and that the password of the account must be changed. I don't know why but I am using SQL Server 2005 so maybe there is some sort of a catch here? The login has the appropriate read and write permissions on my database so ...
In my web.config:
...
</system.web>
<appSettings>
<add key="Econn" value="Server=servername;u
</appSettings>
</configuration>
in my aspx.vb code behind:
Dim sConn As SqlConnection = New SqlConnection(System.Confi
FYI: I have replaced some of the values with fake values for EE and for my db security
I looked at my login in SQL Server 2005 and noticed that I had "Enforce password expiration" checkbox checked. When I tried to uncheck that and click OK I got the error "The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is on"
In my web.config:
...
</system.web>
<appSettings>
<add key="Econn" value="Server=servername;u
</appSettings>
</configuration>
in my aspx.vb code behind:
Dim sConn As SqlConnection = New SqlConnection(System.Confi
FYI: I have replaced some of the values with fake values for EE and for my db security
I looked at my login in SQL Server 2005 and noticed that I had "Enforce password expiration" checkbox checked. When I tried to uncheck that and click OK I got the error "The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is on"
Sounds like you're having problems with that MUST_CHANGE option; it's active in your SQL Server settings, so when you're logging in via your web page, it's expecting you to change the password.
ASKER
thanks. I tried to run this query but it didn't take or like its syntax:
ALTER LOGON sqlapps with check_expiration = off
go
ALTER LOGON sqlapps with check_expiration = off
go
ASKER
>>>Sounds like you're having problems with that MUST_CHANGE option; it's active in your SQL Server settings, so when you're logging in via your web page, it's expecting you to change the password.
exactly. So I'm trying to turn it off...still trying here.
exactly. So I'm trying to turn it off...still trying here.
ASKER
tried this, not sure really how to alter this then:
ALTER LOGON sqlapps with CHECK_POLICY = off
ALTER LOGON sqlapps with CHECK_POLICY = off
It's ALTER LOGIN, not ALTER LOGON ... and if you're going to turn off the CHECK_EXPIRATION, you have to turn off MUST_CHANGE as well.
Again, from Microsoft:
ALTER LOGIN login_name
{
<status_option>
| WITH <set_option> [ ,... ]
}
<status_option> ::=
ENABLE | DISABLE
<set_option> ::=
PASSWORD = 'password'
[
OLD_PASSWORD = 'oldpassword'
| <secadmin_pwd_opt> [ <secadmin_pwd_opt> ]
]
| DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
| NAME = login_name
| CHECK_POLICY = { ON | OFF }
| CHECK_EXPIRATION = { ON | OFF }
| CREDENTIAL = credential_name
| NO CREDENTIAL
<secadmin_pwd_opt> ::=
MUST_CHANGE | UNLOCK
Again, from Microsoft:
ALTER LOGIN login_name
{
<status_option>
| WITH <set_option> [ ,... ]
}
<status_option> ::=
ENABLE | DISABLE
<set_option> ::=
PASSWORD = 'password'
[
OLD_PASSWORD = 'oldpassword'
| <secadmin_pwd_opt> [ <secadmin_pwd_opt> ]
]
| DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
| NAME = login_name
| CHECK_POLICY = { ON | OFF }
| CHECK_EXPIRATION = { ON | OFF }
| CREDENTIAL = credential_name
| NO CREDENTIAL
<secadmin_pwd_opt> ::=
MUST_CHANGE | UNLOCK
So try:
ALTER LOGIN sqlapps WITH MUST_CHANGE=OFF, CHECK_EXPIRATION=OFF
ALTER LOGIN sqlapps WITH MUST_CHANGE=OFF, CHECK_EXPIRATION=OFF
ASKER
I always have a hard time understanding MS syntax when they explain syntax! Can't they format this to be more user friendly when they show syntax
ASKER
just tried that, still doesn't like MUST_CHANGE, doesn't recognize it.
ASKER
I'm looking at BOL as we speak...
This is the article I was referencing:
http://msdn2.microsoft.com/en-us/library/ms189828(en-US,SQL.90).aspx
Note this, though, since it might apply to you:
Important:
A known issue in Windows Server 2003 might prevent the bad password count from being reset after LockoutThreshold has been reached. This could cause an immediate lockout on subsequent failed login attempts. You can manually reset the bad password count by briefly setting CHECK_POLICY = OFF, followed by CHECK_POLICY = ON.
>>still doesn't like MUST_CHANGE, doesn't recognize it
What's the error message you're getting?
http://msdn2.microsoft.com/en-us/library/ms189828(en-US,SQL.90).aspx
Note this, though, since it might apply to you:
Important:
A known issue in Windows Server 2003 might prevent the bad password count from being reset after LockoutThreshold has been reached. This could cause an immediate lockout on subsequent failed login attempts. You can manually reset the bad password count by briefly setting CHECK_POLICY = OFF, followed by CHECK_POLICY = ON.
>>still doesn't like MUST_CHANGE, doesn't recognize it
What's the error message you're getting?
ASKER
I tried running this:
ALTER LOGIN sqlapps with CHECK_POLICY = off
and get this
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.
ALTER LOGIN sqlapps with CHECK_POLICY = off
and get this
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.
Ok, try running these one at a time. First, run this:
ALTER LOGIN sqlapps WITH MUST_CHANGE = OFF
If that works and doesn't give you an error, then run this:
ALTER LOGIN sqlapps WITH CHECK_POLICY = OFF
ALTER LOGIN sqlapps WITH MUST_CHANGE = OFF
If that works and doesn't give you an error, then run this:
ALTER LOGIN sqlapps WITH CHECK_POLICY = OFF
ASKER
for this
ALTER LOGIN sqlapps WITH MUST_CHANGE = OFF
I get this:
Incorrect syntax near 'MUST_CHANGE'.
For this
ALTER LOGIN sqlapps WITH CHECK_POLICY = OFF
I get this:
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.
ALTER LOGIN sqlapps WITH MUST_CHANGE = OFF
I get this:
Incorrect syntax near 'MUST_CHANGE'.
For this
ALTER LOGIN sqlapps WITH CHECK_POLICY = OFF
I get this:
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
AWESOME! worked! Thanks!!
So you reset the CHECK_POLICY option as well? And the login worked from the web page?
ASKER
the login still doesn't work but the check_policy worked. My login gets the same errors as here:
https://www.experts-exchange.com/questions/21670519/Login-fails-for-SQL-2005.html
https://www.experts-exchange.com/questions/21670519/Login-fails-for-SQL-2005.html
MUST_CHANGE
Applies to SQL Server logins only. If this option is included, SQL Server will prompt the user for a new password the first time the new login is used.
CHECK_EXPIRATION = { ON | OFF }
Applies to SQL Server logins only. Specifies whether password expiration policy should be enforced on this login. The default value is OFF.
If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement will fail.
A combination of CHECK_POLICY = OFF and CHECK_EXPIRATION = ON is not supported.
When CHECK_POLICY is set to OFF, lockout_time is reset and CHECK_EXPIRATION is set to OFF.
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.