November 2020 – Max Trinidad – The PowerShell Front

This November, PowerShell 7.1 (GA) will become available, as well as PowerShell 7.2 Preview version. And it will come with some interesting features.
If you want more information on these upcoming releases, check out the following two videos:

* Taking your automation to the next level with PowerShell 7

* PowerShell Unplugged – Challenge Edition

Both videos will give you enough information about the history and what’s coming in PowerShell 7.1.
I guarantee that you won’t be disappointed.

But wait! There’s more. Don’t forget to check out any of the existing modules found in the PowerShell Gallery, such as:

* Microsoft.PowerShell.SecretManagement and Microsoft.PowerShell.SecretStore

* Microsoft.PowerShell.GraphicalTools and Microsoft.PowerShell.ConsoleGuiTools

* Microsoft.PowerShell.UnixCompleters

Remember, PowerShell has become the cross-platform automation tool of choice for Windows, Linux, and macOS.
It’s never too late to get on the PowerShell bandwagon!

You own a personal computer, and you want to learn about SQL Server. Then, you probably installed the SQL Server Developer Edition, which is free. But, there will be times when you may decide to change the computer name. And, because you have already installed SQL Server, you will find there are a few things in the SQL Server configuration that will need to be corrected.

The first sign of something wrong!

In my case, I renamed my computer name from ‘DESKTOP-K6AKFTA‘ to ‘MAXTLAPTOP1‘ with SQL Server previously installed. Then, while executing an SSIS SQL job, the job failed to execute giving me the following error:

Keep in mind, the User-id was added to the SQL Server security logins group with the previous computer name. Here is where you’ll find that something is not right.

Fixing the Computer Name for the User-Id

Use the SQL Server Object Explorer and navigate to the “Security\Logins” folder. Here you can find the local user with the wrong machine name.

To correct the User-Id, just right-click and select “Rename” to change the computer name.

image

Next, right-click in the User-Id, select “Properties“, and then go to “Securables“. Here you’ll find the SQL Server Instance Name is wrong.

image

This needs to be fixed!

Check the current SQL Server Configuration Server Name

To verify the SQL Server configuration system name use the “@@SERVERNAME” function by executing the following T-SQL script:

## - Execute the following T-SQL command:
--
-- Get SQL Server first time "Installed" Servername with Instance Name:
-- Note: Assuming this is run after the system has been renamed.
--
Select @@SERVERNAME CurrentSvrNameWithSqlInstanceName;

--Output:
CurrentSvrNameWithSqlInstanceName
DESKTOP-K6AKFTA\MSSQL2K19A

As stated in the Microsoft documentation, this function will display the value saved after the SQL Server installation. This value is will not be updated if the computer gets renamed.

By executing the above T-SQL script you’ll notice the return will not match the new computer name.

Fixing the SQL Server Configuration Server Name

To fix the SQL Server Configuration “@@SERVERNAME” function value you’ll need to use two of the Security Stored Procedures: “sp_dropserver” and “sp_addserver“. Below, I created a Dynamic SQL (T-SQL) Script solution to take care of this issue:

First, using the “sp_dropserver” to drop the server name:

--
-- Drop the wrong SQL Server Configuration @@Servername:
--
Declare @TSql1 nvarchar(max);
Declare @cfgSvrNameWithSqlInstName nvarchar(50);
Set @cfgSvrNameWithSqlInstName = (Select @@SERVERNAME);
Set @TSql1 = 'sp_dropserver ' + '''' + @cfgSvrNameWithSqlInstName + '''';

print @Tsql1;

exec sp_executesql @TSql1;
GO

Next, I use the “sp_addserver” to add back the correct server name:

--
-- Fix the SQL Server Configuration @@SERVENAME with SqlInstance Name:
--
Declare @TSql2 nvarchar(max);
Declare @fixSvrNameWithSqlInstName nvarchar(50);
Set @fixSvrNameWithSqlInstName = (Select convert(nvarchar, SERVERPROPERTY('servername')));
Set @TSql2 = 'sp_addserver ' + '''' + @fixSvrNameWithSqlInstName + ''''+ ', local';

print @TSql2;

exec sp_executesql @TSql2;
GO

Then, to verify the change works, use the following T-SQL statement:

Select @@SERVERNAME

Or, in SSMS, use the SQL Server Object Explorer, navigate to the “Security\Logins” folder, right-click in the User-Id, select “Properties“, and then go to “Securables“. Then, you’ll see the server name has been fixed.