Changing Service Credentials

Av rating:

Total votes: 18
Total comments: 5

Code download

send to a friend

printer friendly version

Changing Service Credentials

05 September 2007

by Rodney Landrum

or
Service Accounts for Something

Changing SQL Service Credentials and Restarting with T-SQL

It could be that one is speaking hypothetically, as DBAs so often do, and do so well; but it may be that one is responding to a true-to-life emergency change in a Daliesque paranoiac frenzy, but there could easily come a time when you will need to change those service credentials under which your SQL Server services normally run so happily, day in and day out.

The reasons for changing service credentials are obvious, but must be stated in bullet form for therapeutic reasons so that other DBAs, who may find themselves in the same situation as I am, can plead anonymity. I will take the heat for you:

Some DBAs often hop from one company to another.
There are times when DBAs know the password for the service credentials that run SQL Services
There are myriad services on a plentitude of servers that all use the same account.
Sometimes DBAs actually login to servers via RDP with this service password
Active Directory administrators do not always set a specific group policy to prevent account lockouts for the service accounts for SQL Services

Remember those surveys that you fill out when you want to download new/free/trial software that ask “How many SQL Servers do you have in your organization?” If you answer 1-5, you are ok because you have time on your side. You can change the service accounts when a DBA leaves, assuming that with 5 servers you even have a position of “DBA”. In this case, you ARE the DBA and network admin and mail administrator and…However, if you answered 100-500 on the survey it may be time to consider the automation of your management of service accounts, using some simple T-SQL code and some common command line tools.

Before I dive headlong into long strings of code that makes the task of changing service credentials for SQL Services semi-automated, I should post a disclaimer:

What I am going to present should be tried in QA or test environments thoroughly and for managing those 100-500 systems I would encourage using this query with SQL Server Integration Services instead of linked servers with distributed queries.

My partiality to SSIS and aversion to linked servers would be the only reason for the recommendation. For those interested, I have written several articles on using variables and expressions with SSIS to dynamically control connection strings. But that is a topic for the future.

I use four commands (five, if you count SQLCMD which I don’t) to programmatically control (via T-SQL) the reporting, modification and restarting of SQL Services. I know there are many other ways, using SMO or DMO; but I have not made the leap to being a developer and up until recently pronounced C# as C “Pound”. 'Recently' was about 4 years ago -I am not that naive, but I'm sure that you get my point.

The four commands are:

Xp_CmdShell (SQL)
WMIC (Windows)
SC.EXE (Windows)
Net Stop and Net Start

The T-SQL code that incorporates each command is straightforward. In an effort to adhere to Simple-Talk’s motto – SIMPLE – I should outline the logic before giving a full listing. The goals for the code are:

Display a report of all service account credentials for any SQL-related service. This could include MSSQLServerADHelper or SQLBrowser as well as the database engine itself (MSSQLSERVER) and the SQL Server Agent (SQLSERVERAGENT).
Display only the primary SQL service credentials. I have included the database engine, the agent and the SQL Backup Agent, which is a Red Gate service.
Display only services that run under an account value that I can specify.
Change the account information (account name and password) for the primary services.
Change just password for the primary service accounts.
Restart the services.

This type of logic is easily coded and can appear redundant when looking at the full code with varying options. I should also note that I would generally produce a parameterized stored procedure to handle the input criteria to control the flow of the logic in the query. Since, my goal is to roll this ad-hoc query into SSIS, I chose not to use a stored procedure as this would require creating it on each server I would run the code against. For the time being I am comfortable with an ad-hoc query.

Let’s begin by looking at each piece of the T-SQL code, the first being the nine parameters that control the flow and set the credential values.

Figure 1

DECLARE @Acct VARCHAR(30)
-- Lookup Account Variable - Used for Reporting on Specific Accounts.
DECLARE @Primary_SQL SMALLINT
-- Work with only the three main services (Engine, Agent and Red Gate SQL Backup)
DECLARE @Service_Name VARCHAR(100)
-- Used to load service cursor
DECLARE @Change SMALLINT
-- Used as Boolean to trigger a change of the account or password or both
DECLARE @TSQL VARCHAR(500)
-- Used to execute the T-SQL Code
DECLARE @NEW_Acct VARCHAR(40)
-- Sets the new account for the Primary Services
DECLARE @PWD VARCHAR(50)
-- Sets the new password for the Primary Services
DECLARE @Restart SMALLINT
-- Used as Boolean to restart after changes
DECLARE @Change_PWD_Only SMALLINT
-- Used as Boolean to Change the password only.

These nine parameters, documented in code, are set with values prior to executing the T-SQL code. As I'm a DBA of a large organization, my first objective was to programmatically change the service account password when necessary. I have added other features that may be of use; such as simply reporting the service credentials for all SQL services, reporting only for a specified account, or changing the service credentials (account and password or both) for the primary services. I chose to allow service credential changes only for primary services, because the triad (Engine, Agent and Red Gate) are the ones that I know run under specific service credentials. This may not be the case for you and your organization, so modification is expected and encouraged, especially when we get to restarting these services. As we walk through the code, the parameter values will become evident. It is enough for the time being to be aware that the only combination of parameters that will cause a restart would be @Change, @Restart and @Primary_SQL all with a value of 1. When any of these parameters are set to 0 the query will only report on or modify the service credentials. Figure 2 shows sample values.

Figure 2

SET @NEW_Acct = '.\LocalSystem'
SET @PWD = ''
SET @Change = 0
SET @Primary_SQL = 0
SET @Acct = NULL
SET @Restart = 0
SET @Change_PWD_Only = 0

After setting the combination of parameters, we can begin to break apart the three commands that drive the query, starting with xp_cmdshell. As most of us know, xp_cmdhsell is an extended stored procedure that allows us DBAs to run OS level commands as if we were in a CMD window. By default, xp_cmdshell is not enabled on SQL 2005 for good reason. We will have enable it as part of the query for SQL Server 2005. In SQL 2000, xp_cmdshell is not a configuration and is on by default for system administrators. The code to enable xp_cmdshell for SQL Server 2005 at the start of the query, in Listing 1.

Listing 1

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO

So what are we going to use xp_cmdshell for? We are going to populate a temp table with output from the second command, WMIC. WMI, which stands for 'Windows Management Instrumentation', is a framework for monitoring and managing many aspects of Windows and the hardware on which it runs, such as disk subsystems, CPU, memory, and for our use, services. WMIC is a command line tool that taps into WMI. We will use WMCI to populate the table with service information specific to SQL services. The code to create the temp table is in Listing 2. The output of the WMIC command will be inserted as one big string, into the column ironically named Big_String.

Listing 2

IF EXISTS ( SELECT  Name
            FROM    tempdb..sysobjects
            WHERE   name LIKE '#MyTempTable%' )
    DROP TABLE #MyTempTable

CREATE TABLE #MyTempTable
    (
      Big_String NVARCHAR(500)
    )

The code to execute the xp_cmdshell WMIC command, looking for SQL services only, is in Listing 3. Notice that the WMIC command is piped into another command line tool (I know I said only four) called findstr. findstr is similar to ‘grep’ on UNIX system and searches for matching text patterns. In this case we are looking through the WMIC output for the string “SQL”.

Listing 3.

INSERT  INTO #MyTempTable
        EXEC master..xp_cmdshell
             'WMIC SERVICE GET Name,StartName | findstr /I "SQL"'

The output of this command is indeed one long string. With some basic text maniuplation, however, we can clean up the out put and feed it into the remainder of the code which uses the parameters to control the flow adhering to the goals of reporting on and modifying the service accounts.

I use several string functions to display the results into formatted output such as Substring(), charindex() and len(). The same code to generate the reports are repeated with IF...BEGIN…END clauses based again on the parameter values. For example if @Primary_SQL = 1 and @Acct is NULL the code only shows the three primary services mentioned previously, Engine, Agent and Red Gate SQL Backup, see Listing 4.

Listing 4

--Show only Top 3 SQL Services "Engine, Agent and RedGate SQL Backup"

IF @Primary_SQL = 1
    AND @Acct IS NULL
    BEGIN
        SELECT  @@ServerName AS ServerName,
                RTRIM(LEFT(Big_String, CHARINDEX('     ', Big_String))) AS Service_Name,
                RTRIM(LTRIM(RTRIM(SUBSTRING(Big_String,
                                            CHARINDEX('     ', Big_String),
                                            LEN(Big_String))))) AS Service_Account
        FROM    #MyTempTable
        WHERE   Big_String IS NOT NULL
                AND RTRIM(LEFT(Big_String, CHARINDEX('     ', Big_String))) IN (
                'MSSQLSERVER', 'SQLSERVERAGENT', 'SQLBackupAgent' )
    END

Notice I also use the @@Servername value to add in the server on which the code is being run. For global changes executed via linked servers or Integration Services packages, this is useful. The code follows logic to report similarly on non-primary SQL services, such as SQL Browser, and also for specific accounts. By adding a value to the @Acct value, for example, ‘.\LocalSystem’ the code only returns records for services that run under that account. Figure 3 shows the ouptut of a sample report using all services, so a @Primary_SQL value of 0 and @Acct value of NULL.

Figure 3

After the reporting iterations, comes the actual modifications and restarting of the primary SQL services if the parameter values that control modifications are set correctly. The parameters that control this modification logic are combinations of the following:

@Primary_SQL
@Change
@NEW_Acct
@PWD
@Restart
@Change_PWD_Only

Knowing that the code will only ever change the primary services, @Primary_SQL will always need to be set to a value of 1 for any modifications of the services. If not set to 1, the code will be used for reporting only. @Change is another variable that must be set to a value of 1 for any modification to be made. This is a secondary safeguard because this query has, by its very nature, some ominous potential. Time for another disclaimer? @New_Acct is used to set the new account if necessary and @PWD is the new password that goes with the new account. It is possible, as you will see to change the password only without changing the account, hence the @Change_PWD_Only variable. And finally, the almighty @Restart variable. For fear of stating the obvious, @Restart will indeed, like in the film Flatline, kill and then resucitate the primary SQL services with its new guardian angel service account.

Let’s look now at how the code works with these parameters to control the configuration and restarting of the primary services. This is where the third and fourth commands come into play, “sc.exe” and “net stop” and “net start”.

sc.exe is a command line utility that controls the services that run on a Windows system. With “sc” you can query, modify, start and stop any service on a system. The code uses “sc” to change the account or password or both. I chose to use “net stop” and “net start” instead of “sc” to change the configuration as these are more common command line service management tools that administrators are familiar with. Plus I needed them so I could use 4 commands instead of just 3. Good practice. Not “best” practice, but it keeps the knife blade skill set honed to a razor sharp edge.

Listing 5 shows a snippet of code that modifes the service credentials to change both the account and password for the primary SQL services.

Listing 5

IF @Primary_SQL = 1
    AND @Change = 1
    AND @Change_PWD_Only = 0
    BEGIN
        DECLARE Services CURSOR
            FOR SELECT  RTRIM(LEFT(Big_String,
CHARINDEX('     ', Big_String))) AS Service_Name
                FROM    #MyTempTable
                WHERE   Big_String IS NOT NULL
                        AND RTRIM(LEFT(Big_String,
                                       CHARINDEX('     ', Big_String))) IN (
                        'MSSQLSERVER', 'SQLSERVERAGENT', 'SQLBackupAgent' )

        OPEN Services
        FETCH NEXT FROM Services INTO @Service_Name

        WHILE ( @@fetch_status <> -1 )
            BEGIN
                IF ( @@fetch_status = -2 )
                    BEGIN
                        FETCH NEXT FROM Services INTO @Service_Name
                        CONTINUE
                    END
                SET @TSQL = 'EXEC master..xp_cmdshell ''sc CONFIG '
                    + @Service_Name + ' obj= ' + '"' + @NEW_Acct + '" '
                    + 'password= ' + '"' + @PWD + '"'''
                PRINT @TSQL

--Exec and Capture Command Ouput

                INSERT  INTO #HoldErrors
                        EXEC ( @TSQL )

                FETCH NEXT FROM Services INTO @Service_Name
            END

        DEALLOCATE Services

        SELECT TOP 2
                Error_String
        FROM    #HoldErrors
        WHERE   Error_String IS NOT NULL

    END

Notice that this code uses the @Change_PWD_Only parameter with a value of 0. The code, therefore, expects that the @PWD parameter will contain a value, even if blank (‘’). (When seting an account to run as Local System, the values for @NEW_Acct and @PWD would be “.\LocalSystem” and ‘’ respectively as LocalSystem does not require a password).

Another important part of the code at this point will be the use of the #HoldErrors temp table. This table is used to store the output of the executed T-SQL code, stored in the @TSQL variable. Later, we will interrogate the values in the #HoldErrors table to determine if the changes were successful. There is no point (and it would be ludicrous) restarting services if the configurations were not successful. #HoldErrors will let us know if the changes worked and if we should therefore restart. For output we select the top 2 records simply for reporting purposes.

Similar code drives the changing of the password only. This code checks the @Change_PWD_Only variable and if it is 1, then we execute the “sc” command without the “obj” option but pass in the new password stored in the @PWD variable.

NOTE: There are a couple of caveats when changing the account information for services. The user will have to exist, otherwise there will be an error and they have to have been given “Logon as Service” rights.

Now for the scary part….

The code that initiate the restarting of the primary services has the built-in safeguards I mentioned previously plus the error checking of the output of the #HoldErrors table. Listing 6 displays the logic of when the services will be restarted.

Listing 6

--Restart those primary services after changes

IF @Primary_SQL = 1

and @Restart = 1

and @Change = 1

BEGIN

IF EXISTS ( SELECT Top 1

Error_String

FROM #HoldErrors

Where Error_String is not NULL

and ERROR_String like '%FAILED%' )

GOTO End_On_Failure

As you can see, the three controlling variable need to be set to 1 and if there were errors in the output of the sc command inserted into #HoldErrors, we will fall out to End_On_Failure and the next bit of code, see Listing 7, that does the dirty work of restarting will not execute.

Listing 7

        BEGIN
            SET @TSQL =
'EXEC master..xp_cmdshell ''START c:\utilities\ReStart_SQL.CMD'''
            PRINT 'INTO THE ABYSS WE GO....'
            PRINT @TSQL
            EXEC ( @TSQL )
        END
    END

If we assume that the changes to the service accounts were made successfully and the parameters are set to restart, the code will shell out to a batch file. By using the term “shell out” I mean I then need to spawn the batch file to another process outside of the SQL database engine proper. I do this by using the “START” command. Without using “START”, the services would not be able to come back up, due to this little thing I like to call a paradox.

I chose to create the batch file manually and place it in a location on each server. I did not do this merely because I am lazy, but because…yeah, well I am lazy. I know code could be enhanced to create the batch file or, for that matter, build a string and then execute it, but a batch file gives me one advantage: we know that SQL is going to “join the choir invisible” after we initiate the restart. In the batch I can, also add a command, which I indeed have done, to send me an email to let me know that the services have come back up. I use sqlcmd and sp_send_dbmail to send the notification that services have resumed. I also add the data via the @Query option of sp_send_dbmail by selecting @@Servername and GetDate(). Listing 8 shows the steps in the batch file, “C:\utilities\Restart_SQL.CMD”

Listing 8

NET STOP /Y MSSQLSERVER
NET STOP SQLBackupAgent
NET START MSSQLSERVER
NET START SQLSERVERAGENT
NET START SQLBackupAgent
sqlcmd -Q "msdb..sp_send_dbmail @Profile_name = 'DB_Mail',@recipients = 'MyName@MyEmail.com', @Subject = 'Restarted', @Query = 'Select @@Servername + '' restarted at '', GetDate()'"

I know now, after working on this script, that there is always room for enhancement and this may not be for everyone. You'll want to get familiar with how this script runs with many different parameter options, maybe even commenting out the restart section and testing in a QA environment where the accounts and passwords can be changed at will and services restarted. I believe I have filled my event log with the frequent restarts on my local laptop while testing. It took a while to get over the fear of execution, so to speak, which is exactly what it would have been if there was an inadvertent run against a production system. I am now confident enough though, to roll this script into an SSIS package, the outcome of which I hope to post here soon.

The full code follows:

Full Code

--Turn on Cmd_Shell assuming it is not on.
SET Quoted_Identifier OFF
GO

IF SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('ProductVersion')),1,1) = '9'
BEGIN
   EXEC sp_configure 'show advanced options', 1

   RECONFIGURE

   EXEC sp_configure 'xp_cmdshell', 1

   RECONFIGURE WITH OVERRIDE

END

DECLARE @Acct VARCHAR(30)
-- Lookup Account Variable - Used for Reporting on Specific Accounts.
DECLARE @Primary_SQL SMALLINT
-- Work with only the three main services (Engine, Agent and Red Gate SQL Backup)
DECLARE @Service_Name VARCHAR(100)
-- Used to load service cursor
DECLARE @Change SMALLINT
-- Used as Boolean to trigger a change of the account or password or both
DECLARE @TSQL VARCHAR(500)
-- Used to execute the T-SQL Code
DECLARE @NEW_Acct VARCHAR(40)
-- Sets the new account for the Primary Services
DECLARE @PWD VARCHAR(50)
-- Sets the new password for the Primary Services
DECLARE @Restart SMALLINT
-- Used as Boolean to restart after changes
DECLARE @Change_PWD_Only SMALLINT
-- Used as Boolean to Change the password only.

SET @NEW_Acct = '.\LocalSystem'
SET @PWD = ''
SET @Change = 0
SET @Primary_SQL = 0
SET @Acct = NULL
SET @Restart = 0
SET @Change_PWD_Only = 0

IF EXISTS ( SELECT  Name
            FROM    tempdb..sysobjects
            WHERE   name LIKE '#MyTempTable%' )
    DROP TABLE #MyTempTable

CREATE TABLE #MyTempTable
    (
      Big_String NVARCHAR(500)
    )
INSERT  INTO #MyTempTable
        EXEC master..xp_cmdshell
                       'WMIC SERVICE GET Name,StartName | findstr /I "SQL"'

--Show only Top 3 SQL Services "Engine, Agent and RedGate SQL Backup"

IF @Primary_SQL = 1
    AND @Acct IS NULL
    BEGIN
        SELECT  @@ServerName AS ServerName,
                RTRIM(LEFT(Big_String, CHARINDEX('     ', Big_String))) AS Service_Name,
                RTRIM(LTRIM(RTRIM(SUBSTRING(Big_String,
                    CHARINDEX('     ', Big_String),
                    LEN(Big_String))))) AS Service_Account
        FROM    #MyTempTable
        WHERE   Big_String IS NOT NULL
        AND RTRIM(LEFT(Big_String, CHARINDEX('     ', Big_String))) IN (
                'MSSQLSERVER', 'SQLSERVERAGENT', 'SQLBackupAgent' )
    END
ELSE

-- Show only services that match @acct value for primary services

    IF @Primary_SQL = 1
        AND @Acct IS NOT NULL
        BEGIN
            SELECT  @@ServerName AS ServerName,
                    RTRIM(LEFT(Big_String,

                        CHARINDEX('     ', Big_String))) AS Service_Name,
                    RTRIM(LTRIM(RTRIM(SUBSTRING(Big_String,
                         CHARINDEX('     ', Big_String),
                         LEN(Big_String))))) AS Service_Account
            FROM    #MyTempTable
            WHERE   Big_String IS NOT NULL
                 AND RTRIM(LEFT(Big_String, CHARINDEX('     ', Big_String))) IN (
                    'MSSQLSERVER', 'SQLSERVERAGENT', 'SQLBackupAgent' )
                    AND RTRIM(LTRIM(RTRIM(SUBSTRING(Big_String,
                         CHARINDEX('     ', Big_String),
                         LEN(Big_String))))) LIKE '' + '%' + @Acct + '%' + ''
        END
    ELSE

--Show specified @Acct for all SQL Services

        IF @Primary_SQL = 0
            AND @Acct IS NOT NULL
            BEGIN
                SELECT  @@ServerName AS ServerName,
                        RTRIM(LEFT(Big_String, CHARINDEX('     ', Big_String)))
                                             AS Service_Name,
                        RTRIM(LTRIM(RTRIM(SUBSTRING(Big_String,
                            CHARINDEX('     ', Big_String),
                            LEN(Big_String))))) AS Service_Account
                FROM    #MyTempTable
                WHERE   RTRIM(LTRIM(RTRIM(SUBSTRING(Big_String,
                            CHARINDEX('     ', Big_String),
                            LEN(Big_String))))) LIKE '' + '%' + @Acct + '%' + ''
            END
        ELSE

--Show all SQL Services and all accounts
            IF @Primary_SQL = 0
                BEGIN

                    SELECT  @@ServerName AS ServerName,
                            RTRIM(LEFT(Big_String,
                                       CHARINDEX('     ', Big_String))) AS Service_Name,
                            RTRIM(LTRIM(RTRIM(SUBSTRING(Big_String,
                               CHARINDEX('     ', Big_String),
                               LEN(Big_String))))) AS Service_Account
                    FROM    #MyTempTable
                    WHERE   Big_String IS NOT NULL

                END

-- Create Table to Hold Errors for Service Restarts

IF EXISTS ( SELECT  Name
            FROM    tempdb..sysobjects
            WHERE   name LIKE '#HoldErrors%' )
    DROP TABLE #HoldErrors

CREATE TABLE #HoldErrors
    (
      Error_String NVARCHAR(1000)
    )

-- Change Account and Password

IF @Primary_SQL = 1
    AND @Change = 1
    AND @Change_PWD_Only = 0
    BEGIN
        DECLARE Services CURSOR
            FOR SELECT  RTRIM(LEFT(Big_String,
                     CHARINDEX('     ', Big_String))) AS Service_Name
                FROM    #MyTempTable
                WHERE   Big_String IS NOT NULL
                AND RTRIM(LEFT(Big_String, CHARINDEX('     ', Big_String))) IN (
                        'MSSQLSERVER', 'SQLSERVERAGENT', 'SQLBackupAgent' )

        OPEN Services
        FETCH NEXT FROM Services INTO @Service_Name

        WHILE ( @@fetch_status <> -1 )
            BEGIN
                IF ( @@fetch_status = -2 )
                    BEGIN
                        FETCH NEXT FROM Services INTO @Service_Name
                        CONTINUE
                    END
                SET @TSQL = 'EXEC master..xp_cmdshell ''sc CONFIG '
                    + @Service_Name + ' obj= ' + '"' + @NEW_Acct + '" '
                    + 'password= ' + '"' + @PWD + '"'''
                PRINT @TSQL

--Exec and Capture Command Ouput

                INSERT  INTO #HoldErrors
                        EXEC ( @TSQL )

                FETCH NEXT FROM Services INTO @Service_Name
            END

        DEALLOCATE Services

        SELECT TOP 2
                Error_String
        FROM    #HoldErrors
        WHERE   Error_String IS NOT NULL

    END
ELSE
--Change Password Only

IF @Primary_SQL = 1
    AND @Change = 1
    AND @Change_PWD_Only = 1
    BEGIN
        DECLARE Services CURSOR
            FOR SELECT  RTRIM(LEFT(Big_String,
                     CHARINDEX('     ', Big_String))) AS Service_Name
                FROM    #MyTempTable
                WHERE   Big_String IS NOT NULL
                        AND RTRIM(LEFT(Big_String,
                             CHARINDEX('     ', Big_String))) IN (
                        'MSSQLSERVER', 'SQLSERVERAGENT', 'SQLBackupAgent' )

        OPEN Services
        FETCH NEXT FROM Services INTO @Service_Name

        WHILE ( @@fetch_status <> -1 )
            BEGIN
                IF ( @@fetch_status = -2 )
                    BEGIN
                        FETCH NEXT FROM Services INTO @Service_Name
                        CONTINUE
                    END
                SET @TSQL = 'EXEC master..xp_cmdshell ''sc CONFIG '
                    + @Service_Name + ' password= ' + '"' + @PWD + '"'''
                PRINT @TSQL
                INSERT  INTO #HoldErrors
                        EXEC ( @TSQL )

                FETCH NEXT FROM Services INTO @Service_Name
            END

        DEALLOCATE Services

        SELECT TOP 2
                Error_String
        FROM    #HoldErrors
        WHERE   Error_String IS NOT NULL

    END

--Restart those primary services after changes

IF @Primary_SQL = 1
    AND @Restart = 1
    AND @Change = 1
    BEGIN
        IF EXISTS ( SELECT TOP 1
                            Error_String
                    FROM    #HoldErrors
                    WHERE   Error_String IS NOT NULL
                            AND ERROR_String LIKE '%FAILED%' )
            GOTO End_On_Failure

        BEGIN
            SET @TSQL =
               'EXEC master..xp_cmdshell ''START c:\utilities\ReStart_SQL.CMD'''
            PRINT 'INTO THE ABYSS WE GO....'
            PRINT @TSQL
            EXEC ( @TSQL )
        END
    END

End_On_Failure:
PRINT 'Did not Restart'

This article has been viewed 6981 times.

Subject:	help
Posted by:	imtiaz (not signed in)
Posted on:	Friday, September 21, 2007 at 12:26 AM
Message:	Hi, my sql server 2000 service is over now what should i have to do to bring back the sql server. plz show me some tips. thanks

Subject:	User rights?
Posted by:	Andy (view profile)
Posted on:	Friday, September 21, 2007 at 3:29 AM
Message:	Hi Very interessting and dangerous! What happen if you choose a new SQL Service Account without the necessary privileges and windows rights? I could imagine to use this for changing password, but be very prudent if you change the service account Regards Andy

Subject:	xp_regread alternative for services information
Posted by:	Anonymous (not signed in)
Posted on:	Sunday, September 23, 2007 at 12:26 PM
Message:	As an alternative to WMIC, xp_regread is faster and parsing does not need to be performed. This SQL batch works on clusters and handles named instances. Set nocount on Set xact_abort on Declare @registrypath varchar(200) , @namedinstanceind char(1) , @instancename varchar(128) , @svcaccount nvarchar(128) , @startup varchar(128) if object_id('tempdb..#registryentry') is not null drop table #registryentry Create table #registryentry (value varchar(50), data varchar(50)) if object_id('tempdb..#service') is not null drop table #service create table #service ( ServiceName nvarchar(128) , WindowsAccountName nvarchar(128) , StartUpOption nvarchar(128) ) If @@servername is null Or (charindex('\',@@servername)=0) set @namedinstanceind = 'n' Else Begin set @namedinstanceind = 'y' set @instancename = right( @@servername , len(@@servername) - charindex('\',@@servername)) End -- sql server - named instances are applicable Set @registrypath = 'system\currentcontrolset\services\' If @namedinstanceind = 'n' set @registrypath = @registrypath + 'mssqlserver' Else set @registrypath = @registrypath + 'mssql$' + @instancename set @svcaccount = null set @startup = null Insert #registryentry Exec master..xp_regread 'hkey_local_machine' , @registrypath,'objectname' Select @svcaccount = data from #registryentry Delete from #registryentry Insert #registryentry Exec master..xp_regread 'hkey_local_machine' , @registrypath,'start' Select @startup = data from #registryentry Delete from #registryentry IF @svcaccount is not null insert into #service ( ServiceName , WindowsAccountName , StartUpOption ) values ('SQLServer' , @svcaccount , @startup ) -- sql agent - named instances are applicable Set @registrypath = 'system\currentcontrolset\services\' If @namedinstanceind = 'n' set @registrypath = @registrypath + 'sqlserveragent' Else set @registrypath = @registrypath + 'sqlagent$' + @instancename set @svcaccount = null set @startup = null Insert #registryentry Exec master..xp_regread 'hkey_local_machine' , @registrypath,'objectname' Select @svcaccount = data from #registryentry Delete from #registryentry Insert #registryentry Exec master..xp_regread 'hkey_local_machine' , @registrypath,'start' Select @startup = data from #registryentry Delete from #registryentry IF @svcaccount is not null insert into #service ( ServiceName , WindowsAccountName , StartUpOption ) values ('SQLAgent' , @svcaccount , @startup ) -- distributed transaction coordinator ( no named instance) Set @registrypath = 'system\currentcontrolset\services\msdtc' set @svcaccount = null set @startup = null Insert #registryentry Exec master..xp_regread 'hkey_local_machine' , @registrypath,'objectname' Select @svcaccount = data from #registryentry Delete from #registryentry Insert #registryentry Exec master..xp_regread 'hkey_local_machine' , @registrypath,'start' Select @startup = data from #registryentry Delete from #registryentry IF @svcaccount is not null insert into #service ( ServiceName , WindowsAccountName , StartUpOption ) values ('DTC' , @svcaccount , @startup ) -- search (sql server ) Set @registrypath = 'system\currentcontrolset\services\mssearch' set @svcaccount = null set @startup = null Insert #registryentry Exec master..xp_regread 'hkey_local_machine' , @registrypath,'objectname' Select @svcaccount = data from #registryentry Delete from #registryentry Insert #registryentry Exec master..xp_regread 'hkey_local_machine' , @registrypath,'start' Select @startup = data from #registryentry Delete from #registryentry IF @svcaccount is not null insert into #service ( ServiceName , WindowsAccountName , StartUpOption ) values ('SQLSearch' , @svcaccount , @startup ) Select cast( serverproperty ('machinename') as nvarchar(128) ) as machinename , coalesce ( cast( serverproperty ('instancename') as nvarchar(128) ) , 'default') as instancename , ServiceName , WindowsAccountName , StartUpOption from #service

Subject:	This article is great
Posted by:	Manoj Banga (not signed in)
Posted on:	Sunday, September 30, 2007 at 4:07 AM
Message:	Thanks and keep writing the great articles.

Subject:	Simple Script for Setting MSSQL and MSSQLAgent accounts/passwords
Posted by:	Anonymous (not signed in)
Posted on:	Friday, March 07, 2008 at 1:54 AM
Message:	I need a simple script for setting service account credential on the sqlserver and sqlserveragent as well setting passwords. Anyone other there anything like this...