Check_expiration option

  • 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 .

  • 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

  • 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 ?

  • 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

  • Thanks Lars, thats what I wnated to know. I'll try that out and publish the results here. 🙂

  • It worked for me. Apologies for late reply.

  • 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

  • Leave out MUST_CHANGE 🙂

  • 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.

  • 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

  • Its Great working perfectly and thanks Bro( Lars Søe Mikkelsen) for spending your valuable time for me.

  • Hi Lars Søe Mikkelsen,

    My client is getting the connectivity issue bue to the following msg:

    BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device 'SQLBACKUP_61AB46C7-DEAF-4CC8-A628-39B138D8283C'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.)

    Can you please give me your valuable suggestion.

    I will be waiting for your reply Søe Mikkelsen.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply