Search This Blog

Monday, November 20, 2023

Beginner's Introduction to PowerShell Remoting

Beginner's Introduction to PowerShell Remoting


About PowerShell

PowerShell is scripting language and command-line shell/tool by Microsoft. It offers robust capabilities in automating complex and repetitive tasks, thus significantly increasing efficiency and productivity, and hence it's original intended use for the task automation and configuration management.  But you can also use it for your day to day or ad-hoc work, which I often do. 

It is built on the .NET framework or in other words it is a wrapper around .Net functions/methods, making it more user-friendly and simplified interface to underlying .NET framework functionalities. 

For example, the PowerShell command to get current date and time is Get-Date, which is a wrapper method that maps to the System.Console.WriteLine(System.DateTime.Now) if you were writing a equivalent C# code to achieve the same. 

And although many exceptional IT Administrators are also skilled developers, they are generally not recognized as expert programmers. PowerShell gives them with simpler and easier access to the powerful features of the .NET Framework.

In very simple terms, the .NET Framework is like a big library or collection of functions, methods, types, and more, all compiled and stored inside DLL (Dynamic Link Library) files. These DLLs provide a range of ready-to-use functionalities, making it easier for programmers to build and run software, especially on Windows systems. This framework helps avoid the need to write common functions from scratch, streamlining the software development process.

Not everything that is available in .Net Framework is directly exposed or mapped to a PowerShell cmdlet. However, PowerShell also allows you to directly call or reference .NET Framework functions. This capability is one of the powerful features of PowerShell, enabling it to interact seamlessly with the rich set of functionalities provided by the .NET Framework.
 

About PowerShell Remoting

PowerShell Remoting is a feature of PowerShell to run PowerShell commands or scripts on one or multiple remote computers from a local computer. It relies and depends on the Windows Remote Management (WinRM) service, Microsoft's implementation of the Simple Object Access Protocol (SOAP).

90% of work I do using PowerShell involves doing something on remote computers and that means I am almost always using the remoting feature. For me it is a crucial tool for managing and automating tasks across multiple remote systems. By remoting feature, I can execute commands and scripts on remote computers as seamlessly as if they were local. This capability is particularly valuable in medium to large-scale environments, where managing numerous machines individually would be impractical and time-consuming. With PowerShell Remoting, I can simultaneously configure settings, deploy software, and run diagnostics on multiple machines from a single console, vastly improving efficiency and consistency. Furthermore, it employs the robust and secure WS-Management protocol, ensuring that communications between my local machine and remote systems are encrypted and authenticated, providing a safe and reliable way to manage your networked resources. This is important because of the tighter security policies organizations have adopted and the proliferation of the automated cyber security tools to enforce and monitor these policies. 

Setting Up PowerShell Remoting

Before using Remoting, it must be enabled on the remote computer you would like to run PowerShell commands remotely. Fortunately, on server versions of from Windows Server 2008 R2 and above, by default has been the remoting feature installed and enabled, except on Core editions, where this varies based on the specific roles and features. 

Of course, organizations Group Policy Objects (GPO) can change whether remoting is enabled by default and even if it could be enabled at all, even manually.

On client versions of Windows like Windows 7, 8.x, 10, 11 etc. it is installed but not enabled by default for security reasons. 

To manually enabled the PowerShell remoting on a remote computer:  

  1. Launch PowerShell as an administrator.
  2. Type Enable-PSRemoting -Force. This command starts the WinRM service, sets it to start automatically with the system, and creates a firewall rule to allow incoming connections.

How To Use Remoting to Run Commands Remotely

There are primarily two methods to use remoting feature 1) Invoke-Command, which connects to the remote computer, runs the command/s and terminates the connection and 2) By setting up the persistent connection to the remote computer, known-as session/s, using the *PSSession cmdlets:



 





For one-off commands, you can use the Invoke-Command cmdlet:

Invoke-Command -ComputerName <ComputerName> -ScriptBlock { Get-Process }

Remember to replace the "ComputerName" with the actual name or IP address of the remote computer.


The Get-Process cmdlet retrieves all the running processes on the remote computer. You can also target a specific process by providing the process name or process ID (PID), or you can pipe a process object directly to this cmdlet:

Invoke-Command -ComputerName <ComputerName>  -ScriptBlock { Get-Process -Name sqlservr}








Here are a few more examples of using PowerShell remoting:
 
Example 1: Basic Remoting to a Single Computer
 
 
# Start a remote session to remote computer
$session = New-PSSession -ComputerName "ComputerName"
 
# Execute a command on the remote computer
Invoke-Command -Session $session -ScriptBlock {Get-Process}
 
# Close the session
Remove-PSSession -Session
$session
 
 
Example 2: Running a Script Block on a Remote Computer
 
Invoke-Command -ComputerName "ComputerName" -ScriptBlock `
{
      Get-Service | Where-Object { $_.Status -eq 'Running' }
}

 
  
Example 3: Remoting with specific Credentials
 
If you don't pass the credentials, PowerShell takes your currently logged in credentials in order to connect to the remote computer. This of course requires that 1) Your computer and the remote computer are within same AD domain or workgroup, or there is an explicit trust configured between the two and 2) Your login has sufficient permissions on the remote computer to connect and run the specific commands. If that is not the case, you can optionally provide specific credentials to connect to the remote computer, using the Get-Credential command-let, which pops up a the familiar windows login screen:
























$cred = Get-Credential
Invoke-Command -ComputerName "ComputerName" -Credential $cred -ScriptBlock `
{
        Get-WmiObject Win32_LogicalDisk
}

 
 
Example 4: Running a Local Script File on a Remote Computer

Invoke-Command -ComputerName "ComputerName" -FilePath "C:\path\to\your\script.ps1"
 
 
Example 5: Running Command On Multiple Computers

$computers = "Computer1", "Computer2", "Computer3"
 
Invoke-Command -ComputerName $computers -ScriptBlock `
{
           Get-EventLog System -Newest 10
}

 
 
Example 6: Using Sessions for Multiple Invocations
 
$session = New-PSSession -ComputerName "ComputerName"
 
# First command
Invoke-Command -Session $session -ScriptBlock `
{ Get-Process notepad }
 
# Second command
Invoke-Command -Session $session -ScriptBlock `
{ Stop-Process -Name notepad }
 
# End the session
Remove-PSSession -Session
$session
 
 
Example 7: Copying Files to a Remote Session
 
 
$session = New-PSSession -ComputerName "ComputerName"
 
# Copy the file
Copy-Item -Path "C:\local\path\file.txt" -Destination "C:\remote\path\" -ToSession $session
 
# Execute a command to read the file on the remote computer
Invoke-Command -Session $session -ScriptBlock `
{
         Get-Content "C:\remote\path\file.txt"  -tail 10
}
 
# End the session
Remove-PSSession -Session
$session
 

Example 8: Persistent Session for a Script
 
$session = New-PSSession -ComputerName "ComputerName" -Credential (Get-Credential)
 
# Reuse the session for various commands
$scriptBlock = {
    # Series of commands to run
}
Invoke-Command -Session $session -ScriptBlock $scriptBlock
 
# When done, remove the session
Remove-PSSession -Session
$session
 
 
Example 9. Interactive Session
 
Enter-PSSession -ComputerName "ComputerName"
# Now you are working directly on the remote computer's PowerShell prompt.

$env:COMPUTERNAME

# To end the session:
Exit-PSSession


PowerShell Remoting Over SSH

While WinRM is the default transport for PowerShell Remoting, it also supports SSH as a transport. 

But why bother with SSH at all? Maybe not for the Windows computer (although you can),  but to use PowerShell to manage Linux or macOS systems remotely in a secure manner. I so far have had no need to install SQL Server in a Linux environment, except in a lab/play environment for my own education.

 To use SSH:

  1. Install OpenSSH client on local computer and OpenSSH server component on remote computers.
  2. You have SSH keys or credentials for authentication.
  3. Use the -HostName and -SSHTransport parameters with Enter-PSSession or Invoke-Command.

Enter-PSsession <computer name> -UserName <user name>:<domain name> SSHTransport


Is that it? Not really.  But I think this subject deserves it's own article or a series so I will write on soon and add a link here. 

Transport encryption and authentication

By default, all data sent over the network is encrypted using WSMan (the protocol underneath WinRM). When using SSH, the encryption is provided by the SSH protocol.


PowerShell Remoting uses the Kerberos protocol for authentication by default. If Kerberos isn't available, NTLM is used.

Other PowerShell commands that supports remoting

There are several other PowerShell commands that have remoting built-in. For example Get-Process, Get-Service etc. and I consider Copy-Item to be one of them. One way you can find out what commands allows running it on a remote computer (or a list of computers in one go) is to check if they accept ComputerName as an input parameter. 

Get-Command -ParameterName ComputerName










Troubleshooting

Here are a few common issues and their solutions:

  • Access Denied: Ensure the user has permissions to access the remote computer. If using Kerberos, ensure that the computer is domain-joined.

  • Cannot connect: Check if WinRM is running on the remote computer and if there's a firewall rule allowing incoming connections.

  • Double-hop issue: By default, credentials used in a remote session can't be passed to another remote session. Solutions include using CredSSP (with caution) or storing credentials securely and forwarding them.


PowerShell on non-windows systems


PowerShell was originally designed for Windows. Now though  PowerShell is available for Linux, macOS as well as Docket containers. It's important to note that PowerShell Core (versions 6 and above), the cross-platform edition, is the one that supports Linux and macOS. This edition is built on .NET Core and is open-source, catering to a broader range of environments beyond the traditional Windows ecosystem. Windows PowerShell (versions 5.1 and below), on the other hand, is built on the .NET Framework and is specific to Windows environments.



Conclusion


I find knowing how to use PowerShell very useful, especially the remoting feature as it allows me to manage multiple servers from a single location and automate routine tasks.  

However, I wouldn't go as far as saying it's essential to be an effective DBA or even an expert DBA.



Friday, October 6, 2023

How to Downgrade Or Recover a SQL Server Database To A Previous Version Using SqlPackage.exe?

How to Downgrade a SQL Server Database To A Previous Version?

Upgrading a SQL Server database to a newer version is one of the core duties of any DBA, and this holds true for practically any database system. 

But what if this wasn't an upgrade? What if you receive a database (MDFs, LDFs, etc.) or a database backup file/s for a specific version of SQL Server, but you need to use it on a lower version of SQL Server? Or, suppose the database upgrade succeeds, but the application breaks, and none of your database rollback options work, including restoring from the database backups taken just prior to the upgrade. You may still have previous backups, but they don't contain the most recent data. In that case, finding a way to downgrade the database to the prior version might be your most preferred option. You can't simply attach the database (MDFs, LDFs, etc.) or restore that database backup there.

You can however still copy/restore the database to the SQL Server version it was copied from. Then use one of the export/import tools to copy the data from there to the target SQL Server. This obviously requires more work on your part, is complex and is a lengthier process. But it is still a solution that can get the job done, barring any compatibility issues.

Here, however, I want to discuss how to accomplish that using the SqlPackage.exe utility of the Data-tier applications (DAC) as I think it's more efficient and easier in comparison. 


What is SqlPackage.exe?

In a nutshell SqlPackage.exe is a command-line utility that allows you to export/extract an SQL Server database and optionally import into another SQL Server. Sqlpackage.exe is too a data export/import tool with additional features, and it uses the BCP utility (bcp.exe) behind the scene to copy data in and out of a database.

It essentially gives you a command line alternative to some of the APIs and functionalities of the Data-tier applications (DAC) that you may have seen in the SSMS.

Hold on, wait a second. if Sqlpackage.exe (supposedly) only offers a subset of functionality of DAC GUI in SSMS, and if I only need to do this once, why should even consider using this command line utility at all?

The short answer, generally you won't, until and unless you run into errors or issues where GUI is too rigid and inflexible to help you. Please see my notes on potential compatibility errors towards the end of this article.

The intended use case of Sqlpackage.exe  is to migrate a database to another server, whether on-premise or in the cloud, or to upgrade the database to a higher version. However, in these cases the database backup/restore or attach/detach methods are much easier and more efficient, simply because in background SQL Server instance will automatically upgrade the internal structure of the database to match it's version. Therefore, I rarely use SqlPackage.exe for that purpose.

SqlPackage.exe has two modes or application types, either DACPAC or BACPAC. The DACPAC mode, by default, extracts only the database schemas without the data. On the other hand, BACPAC extracts database along with all the data. 

For more information and download instructions to get the latest version, see:

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage


First I will describe the steps for using BACPAC method.

For this demo, I will copy the Northwind database from a SQL Server 2022 instance to a SQL Server 2016 instance.

And, Before you begin, ensure you have SqlPackage.exe installed on your machine or access to a server where it's available. You can usually find it in the "DAC\bin" directory of your SQL Server installation, such as "C:\Program Files (x86)\Microsoft SQL Server\<SQL Server Version>\DAC\bin".


Sunday, September 24, 2023

Enable PowerShell Remoting

Enable PowerShell Remoting

I use PowerShell quite a lot to manage servers, especially SQL Servers. So I need to be able to run PowerShell commands remotely.  Fortunately I don't have to worry about this much on Windows Server 2012 and up, because the PowerShell remoting is enabled by default. On the other hand, if an organization deems PowerShell, and PSRemoting in particular, as a security risk and decides to disable it on purpose. that obivoulsly is a different matter. 

And, any earlier versions of Window Server and all versions of the Windows client operating systems (Home Pro etc.), PowerShell remoting is turned off by default. 

To enable PSRemoting:

Connect / RDP into the computer.

Launch the PowerShell with elevated privileges (Run As Administrator).









Run the command:

Enable-PSRemoting Verbose and Confirm


I added the -Verbose and -Confirm parameters because by default, the Enable-PSRemoting runs silently with no output whatsoever.  

Here is a link to the Microsoft article for more information on Enable-PSRemoting command:


Enable-PSRemoting


And, if you’re working in a workgroup environment, for example, at home, with no domain controller to handle the security and identity, you need to add list of computers you trust. Here is the article that explains how-to, and troubleshooting other potential issues:

Troubleshoot-PowerShell-Remoting


Once it is enable, here is a basic example how to send command to the remote computer:

Invoke-Command -ComputerName Servername -ScriptBlock {'Hello world'}

That's all nice. But how do you check whether it is already enabled or not? PowerShell doesn't provide a direct way to check whether PSRemoting is enabled or not. Indirectly, you can send a test command to the remote computer. 

Invoke-Command -ComputerName Servername -ScriptBlock {'Hello world'}

If errors out, it will display an ugly error message.  The error per se could be due to a completely different reason, maybe you typed in wrong server name, WinRM service is not running, issues with your credentials, firewall, trusts etc. 

Another, indirect method, is to run the Test-WSMan command to see whether WinRM service is running there or not.

Test-WSMan -ComputerName Servername 

This too doesn't guarantee to tell if issue is with PSRemoting setup.

May be some future version of PowerShell will add a command to check status of PSRemoting locally as well as remotely, in secure ways of course.  I am not too hopeful though because it has been enabled by default since Windows 2012, so Microsoft may not see a need for it or deem it a security risk.


Friday, September 22, 2023

Get Configuration Change History From the SQL Server Error Logs

Get Configuration Change History From the SQL Server Error Logs

There are several options if and when you need to see if any configuration change was made in SQL Server.

The simplest maybe to use the SSMS GUI, using its built-in (aka standard) reports:




 








The good thing about this standard report is that it reads from the default trace, which being the "default" setting, would have been already running, that means you don't need to setup and enable it. It is just there, unless of course it was purposely disabled/stopped.

The bad is that the default trace is of limited size, I think the size limit is 20 MB with up to 5 rollover files. You cannot change the size of default trace, you can however create your own trace, in which case better to look at setting up Extended Events instead.

You can also use the SQL Server Auditing and Extended Events features as well, which gives you lot more control on what to capture and to what size. The downside is that you need to set it up and it should be already running by the time there is a need to check if any configuration change was made.

SQL Server also logs anytime there is a configuration change, which you can then view and filter to find the configuration changes by searching for string "Configuration option". You can obviously use the SSMS to open and filter the log file, or even your favorite text editor. And better, you can programmatically read and filter using the sys.xp_readerrorlog:

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-readerrorlog-transact-sql


Here are some examples:

Example 1: Search for entries containing Configuration option

USE master;
EXEC sys.xp_readerrorlog
	0, -- 0 for the current log file, 1 = archive file #1 etc
        1, -- 1 or NULL for SQL Server error log, 2 for the SQL Agent log
        N'Configuration option', -- Search/filter by this string
        NULL, -- Further refine the search/filter condition
        NULL, -- Start time
	NULL, -- End time
	N'desc'; -- Sorts the results by asc for ascending order or 
                 -- desc for descending order









Example 2: A second filter for the Start Time
USE master;
EXEC sys.xp_readerrorlog
	0, -- 0 for the current log file, 1 = archive file #1 etc
        1, -- 1 or NULL for SQL Server error log, 2 for the SQL Agent log
        N'Configuration option', -- Search/filter by this string
        NULL, -- Further refine the search/filter condition
        '2023-09-08 16:00:00', -- Start time
	NULL, -- End time
	N'desc'; -- Sorts the results by asc for ascending order or 
                 -- desc for descending order






Example 3: Further refine/search into the results
USE master;
EXEC sys.xp_readerrorlog
	0, -- 0 for the current log file, 1 = archive file #1 etc
        1, -- 1 or NULL for SQL Server error log, 2 for the SQL Agent log
        N'Configuration option', -- Search/filter by this string
        N'Optimize', -- Further refine the search/filter condition
        NULL, -- Start time
NULL, -- End time N'desc'; -- Sorts the results by asc for ascending order or
                 -- desc for descending order






So, what is the catch here? Every time SQL Server is restarted, it starts a new log file and renames the previous log file to errorlog.1, and the previous errrolog.1 gets renamed to errorlog.2 and so forth, up to 7 logs, which you can increase up to 99. Stored procedure sp_cycle_errorlog can be used to start a new log file in between the SQL Server restarts.  That means 1) The older configuration change entries would get moved to the so called archived error logs, which sys.xp_readerrorlog does allow to read from as well, and 2) Eventually the oldest archived error log files will get removed, unless of course you setup something to store the log entries somewhere.

Of course, you can also use sys.xp_readerrorlog for other purposes as well, for example to see if there have been any errors in last 24 hours.

USE master;
declare @start_time datetime = getdate() - 1
EXEC sys.xp_readerrorlog
    0, -- 0 for the current log file, 1 = archive file #1 etc
    1, -- 1 or NULL for SQL Server error log, 2 for the SQL Agent log
    N'Severity: 16', -- Search/filter by this string
    N'41145', --  Further refine the search/filter condition
    @start_time, -- Start time
    NULL, -- End time
    N'desc'; -- Sorts the results by asc for ascending order 
             -- or desc for descending order




Emergency Access to SQL Server: A DBA's Guide to Regaining Control

Emergency Access to SQL Server: A DBA's Guide to Regaining Control

When I showed a draft of this article to a friend of mine, to get his opinion on it. He gave some interesting feedback and made valid points:

Me: "I've added comments inside the PowerShell code. Do you think it's clear enough, or should I include more screenshots?"

Friend: "The article's informative, but the title could be clearer. It somewhat implies hacking into SQL Server."

Me: "Is that how it comes across? That wasn't my intention."

Friend: "It might just be my perception. Who's your target audience?"

Me: "It's aimed at SQL Server DBAs and those aspiring to be one. The technical depth is necessary."

Friend: "True, but the heavy PowerShell code could be daunting for newcomers."

Me: "I see your point. Technical articles need to balance depth with accessibility. How about I supplement the article with step-by-step explanations or a beginner's guide section?"

Friend: "That sounds like a great idea! It keeps the article comprehensive yet approachable."

Me: "Thanks for the feedback. I'll revise the title to be more specific and refine the content to cater to a broader audience, including those new to SQL Server."

And so I made some changes, added more comments inside the code and updated the title. But I am still bit apprehensive so I would like to make this clear from the beginning: This is really intended for responsible and cautious DBAs, who have legitimate need to access SQL Server but there is no one around with DBA i.e., sysadmin role or any access at the instance level for that matter.  Yes, there can still be loopholes in your SQL Servers if left open, can be leveraged by "ethical" as well as rogue actors alike. Here is a relatively old article discussing some of the ways the SQL Serve security might be compromised:



So, always stay vigilant!

How do you manage and troubleshoot a SQL Server if you don't any access to it at all? You can't.  Even if you have full administrative rights on the server or even in the entire IT network, you cannot connect to the SQL Server if you don't have valid credentials with sysadmin rights.

Unless...... it is a very very old SQL Server version, like SQL Server 7 or older. Back then SQL Server would automatically add the local administrators as sysdba during the installation. You could go back and remove that access, but I don't think most people did.  But back then there was even a bigger security issue with SQL Server, blank password for almighty sa user by default!!!!!
 
Fortunately those dark days are long gone, SQL Server has come a very long way since then and so have we, in our mindsets regarding the security. 

But that is not what I am here to talk about.

So back to the topic, this problem per se is not new and neither is the solution. Most long time DBAs would know the solution already, as they would have run into this issue at some point in their career. 

Let me just describe the solution briefly and get it out of the way as this article is not about that.

The solution requires that you are a member of the local administrators group on the computer where SQL Server instance is installed.  You log into the server, stop and then restart the SQL Server in a single user mode, that temporarily grants local administrators sysadmin permissions. You then connect to the SQL instance with Windows Authentication, and make yourself or any login you like a member of the sysadmin role. Then stop and restart the SQL service in normal mode.  VOILĂ€!
 
Below is a link to a how-to document on this very topic from Microsoft:
 
 
Recently, I got a new gig and had to solve this problem on more than 200 SQL Servers, over a single weekend! Given the short amount of time and a relatively large number of SQL Servers, I decided to write a PowerShell script that I can then call remotely on each SQL Server.  At first the PowerShell code was a relatively short one, without any validation or error checking. It basically restarts the SQL instance in single user mode, adds a AD domain group for DBAs to the sysadmin role then restarts back the SQL Instance and the SQL Agent services:

# INPUTS
$sql_instance_name = 'SQL2022AG01'
$login_to_be_granted_access = 'Contaso\Group-MSSQL-DBAs'

# GET THE NAME FOR THE WINDOWS SERVICE AND THE SQL CONNECTION
if($sql_instance_name -eq "MSSQLSERVER") 
{
    $service_name = 'MSSQLSERVER'
    $sql_server_instance = "."
}
else 
{
    $service_name = "MSSQL`$$sql_instance_name"
    $sql_server_instance = ".\$sql_instance_name"
}

$sql =  "CREATE LOGIN [$login_to_be_granted_access] FROM WINDOWS; ALTER SERVER ROLE sysadmin ADD MEMBER $login_to_be_granted_access; "

Stop-Service -Name $service_name -Force

# START SQL SERVER IN SINGLE USER MODE
net start $service_name /f /m
Start-Sleep 2
sqlcmd.exe -E -S $sql_server_instance -Q $sql

# RESTART SQL SERVICES IN NORMAL MODE
$service = Start-Service -Name $service_name -PassThru
Start-Service -Name $service.DependentServices[0].Name

Note: Restarting a Windows service requires administrator privileges, including for the process from where the service is being restarted from, which in this case is PowerShell and therefore it needs to be launched as “Run As Administrator”:






















Later, I added more code for validation and error checking and an option to create a SQL Login instead of a Windows Login.  So, now the script is longer than maybe it should be:


##### CAUTION: THE SCRIPT WILL STOP AND RESTART YOUR SQL SERVER INSTANCE!!!!!!!!!
<#
THIS SCRIPT IS INTENDED TO GET ACCESS TO SQL SERVER ONLY IF 
YOU DON'T HAVE SYSADMIN PERMISSION. SO ONLY USE THIS IN EMERGENCY

ON THE PLUS SIDE, IT HAS OPTION TO PROMPT YOU FOR CONFIRMATION BEFORE 
STOPPING THE SQL INSTANCE

REQUIREMENTS:.

1. YOU MUST HAVE THE LOCAL ADMINISTRATOR RIGHTS ON THE SERVER
2. YOU ARE RUNNING THE SCRIPT LOCALLY ON THE SERVER, OR 
   THROUGH THE INVOKE-COMMMAND CMDLET AND THE PSREMOTING
   MUST BE ENABLED ON THE TARGET SERVER, WHICH IT SHOULD BE BY 
   DEFAULT BE ON WINDOWS SERVER 2012 AND UP

3. THE POWERSHELL MUST BE LAUNCHED WITH ELEVATED ADMINISTRATIVE PRIVILEGES

PARAMETERS: ONLY THE FIRST PARAMETER, $login_to_be_granted_access, IS REQUIRED

1.  $login_to_be_granted_access --> This can be a Windows Login or SQL Login
                                    $sql_login_password is required for SQL Login

2.  $sql_instance_name -----------> Only specify the sql instance name without the server name
                                    If omitted, the default instance is assumed

3.  $confirm ---------------------> $true or $false to prompt for confirmation

Optional: Only required for SQL Login
4.  $sql_login_password ----------> Password for the SQL Login

#>
param (
  [string] $login_to_be_granted_access = 'sqladmin',
  [string] $sql_instance_name = 'SQL2022AG01',  
  [Boolean] $confirm = $true,
  [string] $sql_login_password = 'WA1!!1P7JRjN7F4eibEES&IxU%Elgw6b#'
)


# set the default preferences
$ErrorActionPreference = 'Stop'
$WarningPreference     = 'Continue'
$InformationPreference = 'Continue'

# if value for sql_instance_name is blnak then assume the default instance
if (-Not ($sql_instance_name)) {$sql_instance_name = 'MSSQLSERVER'}

# if value for $confirm is blank then assume $true
if ($confirm -eq $null) {$confirm = $true}


$msg = 'Computer Name: ' + $env:COMPUTERNAME
Write-Information $msg
$msg = 'SQL Instance Name: ' + $sql_instance_name
Write-Information $msg

Write-Information ""

# SHOW PROMPT IF $confirm IS TRUE
if ($confirm -eq $true)
{
    $valid_responses = "Yes", "yes", "No", "no"
    do {
        Write-Warning "##### CAUTION: THE SCRIPT WILL STOP AND RESTART YOUR SQL SERVER INSTANCE!!!!!!!!!"
        $response = Read-Host -Prompt "Are you sure you want to continue (Yes/No) ?"
        if(-not $valid_responses.Contains($response)){write-host "Please enter Yes or No"}
    } until ($valid_responses.Contains($response))

    if ($response -in ('No', 'no') )
    {
        return
    }
}
else
{
    Write-Warning '$confirm is false therefore all prompts will be suppressed.'
    Write-Information ""
}


# LETS DO A BIT OF VALIDATION
if (-not ($sql_instance_name) -or (-not ($login_to_be_granted_access)))
{
    Throw 'Error: Values for $sql_instance_name and $login_to_be_granted_access are required'
}

if (-not ($login_to_be_granted_access.Contains('\')) -and (-not ($sql_login_password)))
{
    Throw 'A password must be given for SQL Login'

}

# CHECK IF RUNNING POWERSHELL IN ELEVATED PRIVILEDGES MODE
if(-Not (([System.Security.Principal.WindowsIdentity]::GetCurrent()).Owner -eq "S-1-5-32-544"))
{
    Throw 'Error: Powershell must be launched in elevated privileges mode'
}


# GET THE NAME OF THE WINDOWS SERVICE AND THE SQL CONNECTION
if($sql_instance_name -eq "MSSQLSERVER")   # DEFAULT INSTANCE
{
    $service_name = 'MSSQLSERVER'
    $sql_server_instance = "."
}
else                                       # NAMED SQL INSTANCE 
{
    $service_name = "MSSQL`$$sql_instance_name"
    $sql_server_instance = ".\$sql_instance_name"
}


Write-Information "SQL Server: $sql_server_instance"
Write-Information "Serivce Name: $service_name"
Write-Information ""

# GET THE SERVICE OBJECT AND THE DEPENDENT SERVICES
$sql_service = Get-Service -Name $service_name
$dependent_services = $sql_service.DependentServices

# 
# EXTRA CHECK: STOP IF THE SERVICE IS NOT FOUND
if(-Not ($sql_service))
{
        Throw "Error: Please ensure the sql instance $sql_instance_name is valid and a windows service with name $service_name exists..."
}


$msg = "Service Status: " + ($sql_service.Status)
Write-Information $msg

$msg = "Service Startup Type: " + $sql_service.StartType
Write-Information $msg

Write-Information ""

# IF THE SERVICE IS DISABLED, PROMPT TO RE-ENABLE IT IN MANUAL MODE
if($sql_service.StartType -eq 'Disabled')
{

    Write-Warning "SQL Instance sql_instance_name is currently disabled...."
    
    if ($confirm) {Set-Service -Name $service_name -StartupType Manual -Confirm}
    else {Set-Service -Name $service_name -StartupType Manual}
    
    $sql_service.Refresh()

    if($sql_service.StartType -eq 'Disabled')
    {
           Throw "Error: Script cannot continue when SQL Instance is in Disabled mode."
    }


}

# STOP THE SERVICE ONLY IF IT IS RUNNING
# PROMPT TO CONFIRM BEFORE STOPPING THE SERVICE

if($sql_service.Status -eq 'Running')
{
    Write-Warning "Stopping service: $service_name"
    Write-Warning "Any dependent services will also be stopped..."
    
    if ($confirm) {Stop-Service -InputObject $sql_service -Confirm -Force}
    else {Stop-Service -InputObject $sql_service -Force}


    Write-Information ""
    Write-Information "STOP-SERVICE MAY RUN IN ASYNC MODE SO LETS SLEEP FOR FEW SECONDS..."
    Start-Sleep 5
    
    # CHECK TO MAKE SURE THE SERVICE IS NOW STOPPED 
    $sql_service.Refresh()

    if($sql_service.Status -ne "Stopped")
    {
        throw "Error: SQL instance service $service_name must be in stopped state before continuing...."
    }

}



#  A WINDOWS SERVICE CAN ONLY BE STARTED IF IT'S START UP TYPE IS MANUAL, AUTOMATIC OR DELAYED AUTOMATIC START 
#  SO, CONTINUE ONLY IF THE START UP TYPE IS NOT ONE OF THEM

$sql_service.Refresh()
if($sql_service.Status -ne 'Running' -and $sql_service.StartType -in ('Manual', 'Automatic'))
{
    
    Write-Warning  "Starting SQL Service in single user mode..."
    Write-Information ""
    net start $service_name /f /m
    Start-Sleep 2

    # CHECK TO MAKE SURE THE INSTANCE IS NOW RUNNING 
    $sql_service.Refresh()
    if($sql_service.Status -eq "Running")
    {

        if ($login_to_be_granted_access.Contains('\'))
        {
            $sql =  "CREATE LOGIN [$login_to_be_granted_access] FROM WINDOWS; ALTER SERVER ROLE sysadmin ADD MEMBER $login_to_be_granted_access; "
        }
        else
        {
            $sql = "CREATE LOGIN [$login_to_be_granted_access] WITH PASSWORD=N'$sql_login_password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF"
            $sql += "; ALTER SERVER ROLE sysadmin ADD MEMBER $login_to_be_granted_access; "
        }


        $msg = "Adding $login_to_be_granted_access to the SYSADMIN role in SQL Server..."
        Write-Information $msg

        sqlcmd.exe -E -S $sql_server_instance -Q $sql

        Write-Information ""
        Write-Information "Restarting the SQL instance in normal mode..."
        net stop $service_name
        net start $service_name

        # Restart any dependenT service that were running... 
        foreach ($dependent_service in $dependent_services)
        {
            if($dependent_service.Status -eq 'Running')
            {
                $dependent_service_name = $dependent_service.Name
                
                # Check one more time to make sure it's not already running...
                if ((Get-Service -Name $dependent_service_name).Status -ne 'Running')
                {
                    $msg = "Starting dependent service: $dependent_service_name"
                    Write-Information $msg
                    $dependent_service.Start()
                }
            }


        }
    }
    else
    {
        Throw "Error: SQL Instance is not running...."
    }
}


Now what? How do you use it then? If it is just one or two SQL Servers, you can simply copy/paste it into an elevated PowerShell session, and edit the param block with the appropriate values.

param (
  [string] $login_to_be_granted_access = 'sqladmin',
  [string] $sql_instance_name = 'SQL2022AG01',  
  [Boolean] $confirm = $true,
  [string] $sql_login_password = 'WA1!!1P7JRjN7F4eibEES&IxU%Elgw6b#'
)

Note:  You can give a Windows login for the $login_to_be_granted_access parameter, as long as it contains a "\" in it, the script will know it is a Windows login.



Then click the green Run Script button or hit F5 key:































It will display a warning and ask if you want to continue:









It will also ask to confirm again before stopping the SQL Service.

You can disable the confirmation prompts by setting $confirm = $false, which is what I would do when running this in non-interactive mode, especially in a batch mode when running it against multiple SQL Servers at the same time. In the following example, the script is saved in a file "add user to sql sysadmin role.ps1, then using the Invoke-Command, I can run it on a remote server (highlighted):



$script_file_path = "add user to sql sysadmin role.ps1"
Invoke-Command -ComputerName SQLServerVM01 -FilePath $script_file_path `
               -ArgumentList 'sqladmin', 'SQL2022AG01', $false, 'WA1!!1P7JRjN7F4eibEES'






























To run the script on multiple SQL Servers, you can save the list of SQL servers, instance names and login name in a CSV file. For integrated/Windows logins, the LoginName needs to be in "ServerName\UserName" or "Domain\UserName" format and must contain a "\" as that is what my script is using to dynamically determine whether is is Windows login or a SQL login. For example:
















Then use the following PowerShell code to execute the script against all servers listed in the CSV file:

$script_file_path = "add user to sql sysadmin role.ps1"
$csv_data = Import-Csv -Path 'C:\Users\dummy\Documents\myServers.csv'

foreach($csv_row in $csv_data)
{
    Invoke-Command -ComputerName $csv_row.ServerName -FilePath $script_file_path `
                   -ArgumentList $csv_row.LoginName, $csv_row.SQLInstance, `
                                 $false, $csv_row.Password
}


I hope you find this useful in times of need. If you see or come across a bug in this PowerShell code, please let me know.