SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Check_expiration option


Check_expiration option

Author
Message
sqlnaive
sqlnaive
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6465 Visits: 2774
Hello friends, I want to create a SQL db user to be dedicatedly used by application only. I am providing script to dba to execute this:
CREATE LOGIN appuser WITH PASSWORD = 'appuser' MUST_CHANGED, DEFAULT_DATABASE = 'TestDB'
If i use must_change in my script, CHECK_EXPIRATION must be set to ON ? Since I'm creating it for application, how can I avoid it [I think check_expiraiton being ON means it will expire after a time interval].
Lars Mikkelsen-445157
Lars Mikkelsen-445157
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 48
Hi sqlnaive

You cannot use the MUST_CHANGE option without also using CHECK_EXPIRATION=ON.
Furthermore, for clarification; CHECK_EXPIRATION=ON means that the password, not the login, will expire and has to be changed regularly.

Since the login is to be used by an application, I would suggest you simply leave out MUST_CHANGE (if it's OK not to use CHECK_EXPIRATION=ON in your company). Our auditing doesn't allow the CHECK_EXPIRATION=OFF.


Kind regards

Lars Søe Mikkelsen
sqlnaive
sqlnaive
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6465 Visits: 2774
We give our scripts to system DBAs to run. So we don't want them to know what our password is, thats why must_change option. Once we changed the password, can we put the check_expiration off again ?
Lars Mikkelsen-445157
Lars Mikkelsen-445157
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 48
sqlnaive (8/1/2011)
We give our scripts to system DBAs to run. So we don't want them to know what our password is, thats why must_change option. Once we changed the password, can we put the check_expiration off again ?

Hi again

Okay, then you need to have the DBAs create the login like this:

CREATE LOGIN [yourlogin] WITH PASSWORD=N'somepassword' MUST_CHANGE, DEFAULT_DATABASE=[yourdb], CHECK_EXPIRATION=ON



Then proceed to login and change the password.

Finally have the DBAs run this:

ALTER LOGIN [yourlogin] WITH CHECK_EXPIRATION=OFF




Kind regards

Lars Søe Mikkelsen
sqlnaive
sqlnaive
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6465 Visits: 2774
Thanks Lars, thats what I wnated to know. I'll try that out and publish the results here. :-)
sqlnaive
sqlnaive
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6465 Visits: 2774
It worked for me. Apologies for late reply.
vamsi.l2dba
vamsi.l2dba
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 191
hi I tried set SA and PSW its working great to change the password. The password has changed but when I tried to login it is asking new psw and can't login. getting error 18464.



ALTER LOGIN [sa] WITH CHECK_EXPIRATION=ON



ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'NewPassword' MUST_CHANGE
GO
Lars Søe Mikkelsen
Lars Søe Mikkelsen
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 493
Leave out MUST_CHANGE Smile
vamsi.l2dba
vamsi.l2dba
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 191
thanks Bro it is working great.....


I am having a small issue I cannot login in to my instance. I am getting the following error in my errorlog.

Error: 26023, Severity: 16, State: 1.
2013-08-01 14:38:27.65 Server Server TCP provider failed to listen on [ 'any' <ipv6> 1433]. Tcp port is already in use.
2013-08-01 14:38:27.65 Server Error: 17182, Severity: 16, State: 1.
2013-08-01 14:38:27.65 Server TDSSNIClient initialization failed with error 0x2740, status code 0xa. Reason: Unable to initialize the TCP/IP listener. Only one usage of each socket address (protocol/network address/port) is normally permitted.

2013-08-01 14:38:27.65 Server Error: 17182, Severity: 16, State: 1.
2013-08-01 14:38:27.65 Server TDSSNIClient initialization failed with error 0x2740, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Only one usage of each socket address (protocol/network address/port) is normally permitted.

2013-08-01 14:38:27.65 Server Error: 17826, Severity: 18, State: 3.
2013-08-01 14:38:27.65 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2013-08-01 14:38:27.65 Server Error: 17120, Severity: 16, State: 1.
2013-08-01 14:38:27.65 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
2013-08-01 14:38:27.76 spid13s A new instance of the full-text filter daemon host process has been successfully started.
Lars Søe Mikkelsen
Lars Søe Mikkelsen
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 493
It seems an instance of SQL Server is already using the port. Each instance must have its own port.

Try changing the port of either the running or the new instance:

http://msdn.microsoft.com/en-us/library/ms345327(v=sql.100).aspx
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search