Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Check_expiration option Expand / Collapse
Author
Message
Posted Monday, August 1, 2011 3:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
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].
Post #1151682
Posted Monday, August 1, 2011 4:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 3:27 AM
Points: 4, 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
Post #1151689
Posted Monday, August 1, 2011 4:46 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
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 ?
Post #1151694
Posted Monday, August 1, 2011 5:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 3:27 AM
Points: 4, 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
Post #1151699
Posted Monday, August 1, 2011 5:35 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
Thanks Lars, thats what I wnated to know. I'll try that out and publish the results here.
Post #1151728
Posted Monday, August 22, 2011 1:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
It worked for me. Apologies for late reply.
Post #1163129
Posted Wednesday, July 31, 2013 9:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 29, 2014 1:00 AM
Points: 4, Visits: 133
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

Post #1479526
Posted Thursday, August 1, 2013 3:32 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 5:22 AM
Points: 70, Visits: 484
Leave out MUST_CHANGE :)
Post #1479844
Posted Thursday, August 1, 2013 3:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 29, 2014 1:00 AM
Points: 4, Visits: 133
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.
Post #1479849
Posted Thursday, August 1, 2013 4:05 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 5:22 AM
Points: 70, Visits: 484
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
Post #1479856
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse