Installing PowerShell Module Without Internet Access

30 Sep


I need to install a new PowerShell module onto a server that has no access to the Internet, so I am unable to use the Install-Module command.


For a task that I have, I require the ‘SQLServer’ PowerShell module, as the older ‘SQLPS’ module does not have the CmdLets that I’m after.

The ultimate installation target (after local development and testing) is a server that has no Internet access, for security reasons. Therefore I cannot simply use the PowerShell ‘Install-Module’ command, which would take care of the download and installation.

I have read several strange articles on various blog sites, describing methods of achieving this that I (with my limited PowerShell experience) could not understand.

With some vague, hinted-at solutions I have discovered that in reality it is very simple and worth documenting for others that encounter a similar situation.


For this demonstration I’m using a laptop that has a virtual machine (VM) installed, which I can treat as two different environments – the VM, which will have internet access disabled and the laptop, which will have internet access.

First of all, the particular module that I’m after requires a minimum PowerShell version of 5, so I’ll check what version I have installed on my VM test system.

Of course, initially the module has to be downloaded, using a machine that does have internet access. For this demonstration, that is the laptop.

First of all, I’ll confirm that I don’t already have the module installed on the server, by using ‘Import-Module’.

The message ‘The specified module ‘sqlserver’ was not loaded because no valid module file was found in any module directory’ means that it couldn’t find the module in any of the directories it uses, or it has been installed but PowerShell is not a high enough version to access it. That is why we have the check on the version at the beginning.

So where does PowerShell load modules from?

PowerShell has a list of directories that it uses for loading modules, which can be examined with the following command. This shows all of the directories that PowerShell will use for locating the required module.

Downloading the module

To download the module to a specified folder, the ‘Save-Module’ command is used. In the example I specify the destination as ‘C:\TempDB’, because I want to compress and copy it elsewhere afterwards.

Because this laptop has never downloaded via PowerShell before I also see a message that requests I also install the required Nuget provider – you might not see this on your system. In my example I confirm I require that too, in which case it is installed automatically and then downloads the module that I requested.

After this download completes, there is now a folder named ‘SQLServer’ within ‘C:\Temp’. Next, I compress this into ‘SQLServer.Zip’, to make it easier to copy to the destination location.

Copying the module to the target server

There are several ways to copy the file from one server to another. Within my test environment I copy the zipped file from the laptop to the VM by using the ‘Copy-VMFile’ command. In this example the VM is named “Humongous” and the file is being copied from “C:\Temp” on the laptop to “C:\Temp” on the VM.

Now, within the VM, there is a file “C:\Temp\”. I extract these, within the “C:\Temp” folder, producing the PowerShell module (a folder named ‘SQLServer’ and several sub-folders), although currently in the wrong location.

Where should the files be installed?

PowerShell uses certain folders when a module is used. To find which libraries it uses, the PowerShell environmental variable ‘PSModulePath’ can be called. Split the response up by the ‘;’ to make it easier to read.

The first folder listed is local to the account used (‘Student’), so this folder is only available to the ‘Student’ account. To ensure the module is available to all users, we’ll place it in the second folder listed – ‘Program Files\WindowsPowerShell\Modules’.

Final Step for Installation

To make the module available for PowerShell, all that is now needed is to copy the ‘SQLServer’ folder from the ‘C:\Temp’ folder to ‘Program Files\WindowsPowerShell\Modules’.

Confirming the Installation

Now that the module is in a library that PowerShell uses, it can be loaded.

Import-Module will load the module and Get-Module will then list the commands that are available.


Although having Internet access for a PowerShell system is nice, it is not vital and it is relatively easy to install a module without direct Internet access.

Availability Groups Can Use Snapshot Isolation

7 Aug

Whilst reading up on the finer details of High Availability Groups (HA Groups) I have discovered that it can, under certain circumstances, use Snapshot Isolation Level and therefore, row versioning. This has implications with your monitoring and maintenance tasks.
Continue reading

XML AUTO – Basic Examples

20 Jun


Continuing my series of XML articles I’m going to move from XML RAW to XML AUTO with a handful of examples that share features between the two. Continue reading

XML RAW, ELEMENTS and ROOT – Basic Examples

29 Apr


Most systems that I have worked with make use of the XML data type. I don’t find it to be the most intuitive data type to work with but most aspects of it can be built upon from a few basic examples. This will be part of a developing series that will cover XML and JSON. Continue reading

Finding the SQL Server Error Log

27 Mar

Having attempted to start an old test instance from SQL Server Configuration Manager, I received the wonderful error “The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details

Error From Configuration Manager

More helpful than some message you can get – at least it tells me where to look.

So, where is the error log for SQL Server? As I rarely need to access it and always forget how to find it, this small article has been written. I write these articles for myself – if anybody else finds them useful then that’s a bonus.

Configuration Manager is where the startup parameters can be seen, for the instance in question. The ‘-e’ parameter is the location of the error logs is stored.

Showing the location of the error log

So, looking in the file ‘ERRORLOG’, in the given location I can see that the creation of TempDB failed, due to a permissions issue (access denied).

Permissions issue with TempDB

To resolve this issue I checked the folder and the existing TempDB files were set to read-only. Who knows how that happened but the solution was easy enough. Having removed those files (because TempDB is re-created every time) the instance was able to start correctly and all is good in my world.

Of course, this won’t be the solution to every problem but at least you now know where to look for more information.

Dynamic Data Masking – Basic Examples

26 Nov


As part of my research into the various methods of protecting sensitive data, I’ve taken a look at dynamic data masking. A short demonstration and some observations are in the following article.


One aspect of protecting data is ensuring that it is only seen by those that should be able to see it – people only have access to the information that they need, according to pre-defined security levels. This has become a far more prominent issue with the advent of GDPR, although it should have always been a major focus of any organisation that stores or handles personally identifiable information (PII).
Microsoft introduced dynamic data masking with SQL Server 2016, which introduces a method of masking data for those accounts that do not have the required permissions.

Test Environment

Create a simple test database, and a database user that is a member of db_datareader.

Listing 1: Create Initial Test Database and User




ALTER ROLE [db_datareader] ADD MEMBER [TestUser]

Next, two tables are to be created. These tables are deliberately rather basic, without indexes or foreign key constraints – just concentrating on the required pieces for this article.

Listing 2: Create Test Tables

CREATE TABLE Characters(
ID              INT IDENTITY(1,1) NOT NULL,
Forename        VARCHAR(50) MASKED WITH (FUNCTION = 'partial(1,"!!",0)') NOT NULL,
MiddleName      VARCHAR(50) MASKED WITH (FUNCTION = 'default()') NULL,
Surname         VARCHAR(50) MASKED WITH (FUNCTION = 'partial(2,"XYZ",1)') NULL,
Interests       INT  MASKED WITH (FUNCTION = 'random(20,30)') NULL,
Email           VARCHAR(50) MASKED WITH (FUNCTION = 'email()') NULL,
Phone           VARCHAR(30) MASKED WITH (FUNCTION = 'default()') NULL,
DOB             Date	MASKED WITH (FUNCTION = 'default()') NULL,
Salary          INT     MASKED WITH (FUNCTION = 'default()') NULL,
AdditionalNotes	VARCHAR(200) NULL,
Age AS (DATEDIFF(year, [DOB], Getdate()))


The two tables have a MASKED WITH option, against those columns that I want to ‘disguise’ from those user accounts that do have the required permission. At this moment, there are four functions that can be used to mask data:

Partial – replaces the column data with the characters supplied in the second parameter (“!!” for Forename and “XYZ” for Surname), displaying the actual data for the specified number of characters from the start of the column data and the end of the column data, as specified in the first and third parameters. So, the Forename column will display the first character of the true data, then the characters “!!” for the rest of the data – because the third parameter is zero.
Default – replaces the column data dependent upon the type of data column – “XXXX” for character type columns, the number zero for numeric columns, ASCII zero for binary columns and 01-10-1900 00:00:0000000 for date and time columns.
Email – Shows the first character of the email address and uses ‘.com’ as the suffix, with “XXX@XXXX” for the remainder of the address.
Random – Displays a random number from the supplied from/to range. So, the ‘Interests’ column will display a random number from 10 to 30.

Now we add the data to these tables:

Listing 3: Insert the test data

INSERT INTO [dbo].[Interests]([LIKES])
VALUES ('Carrots'),

INSERT INTO [dbo].[Characters]([Forename], [MiddleName], [Surname], [Interests], [Email], [Phone], [DOB], [Salary], [AdditionalNotes])
VALUES ('Bugs', NULL, 'Bunny', 1, '', '555-001-002-003', '20 Apr 1938', 240000,NULL), 
       ('Daffy',NULL,'Duck',2,'','555-123-456 Ext 27', '17 Apr 1937', 200000, NULL), 
       ('Elmer','J','Fudd',3,'','555-1212-3434','17 Jul 1937', 160000, 'Likes everybody to be very quiet'),
       ('Wile', 'E', 'Coyote', 3, '','555-565656-345','17 Sep 1949', 185000, 'Astronomical health insurance');


Querying the Data

At this point in time, because I am a member of the sysadmin role, the masking is not evident. If I run a query against this data I can see all of the data in all of the columns:

Listing 4: Query the test data as sysadmin

FROM Characters c
LEFT OUTER JOIN Interests i on i.ID = c.Interests;

Figure 1: Result from Listing 4

Query result as sysadmin

If I now switch to the TestUser user, which does not the required permission to view masked data, the impact of dynamic data masking becomes apparent.

Listing 5: Query the test data as TestUser


FROM Characters c
LEFT OUTER JOIN Interests i on i.ID = c.Interests;


Figure 2: Result from Listing 5

Query result as TestUser

Now we can see the impact of the mask functions.

It is important to note that the masking only shows itself at the presentation of the data. The join still works, even though it looks like the column ‘Id’ of the table ‘Interests’ is always zero. It isn’t – it is only shown as that to those that are not permitted to see the original data.

Note that the ‘Age’ column is masked, even though a mask was not specified. Because one of the elements of the computed column is using a mask (‘DOB’), the value displayed by the computed column will be masked too.

Removing a Mask from a Column
To remove a mask completely from a column, there is a DROP MASKED option with ALTER COLUMN.

Listing 6: Removing the mask from a column


FROM Characters c
LEFT OUTER JOIN Interests i on i.ID = c.Interests;


ALTER TABLE Characters   


FROM Characters c
LEFT OUTER JOIN Interests i on i.ID = c.Interests;


Figure 3: Result from Listing 6 – showing un-masked forename column

Before and After un-masking column

Granting Permission to View Masked Data

To give permission for a user to view masked data the UNMASK permission is required.

Listing 7: Assigning Permission to View Masked Data


FROM Characters c
LEFT OUTER JOIN Interests i on i.ID = c.Interests;




FROM Characters c
LEFT OUTER JOIN Interests i on i.ID = c.Interests;


Figure 4: Result from Listing 7

Before/After UNMASK Permission


Even if a user does not have UNMASK permission, if they are able to execute SELECT/INTO and create a new table, they are able to extract the data and view it but it will be stored in that table as the masked values – the original data will not be written to that table unless the user has UNMASK permission. When a table is created via SELECT/INTO, the MASK properties for the columns are not copied across to the new table.

Listing 8: Restricted User Copying Data to New Table

INTO dbo.CharactersNoMaskCopy FROM
FROM [dbo].[CharactersNoMaskCopy];

Figure 5: TestUser View of Copied Data

Values written to new SELECT/INTO target

Figure 6: New table without mask properties

SELECT/INTO target without mask

Execution Plan

There does appear to be an overhead, detailed within the execution plan. Although this small demonstration will not show a significant difference, it may be worth further investigation, if you are concerned about any performance impact from applying masked data columns.

Figure 6: Query Plan Expressions – no Data Masking

Query Plan Expressions Without Masking

Figure 7: Query Plan Expressions – Data Masking

Query Plan Expressions With Data Masking


Dynamic data masking creates a simple and easy method of protecting data from unauthorised viewing but it is rather basic. It does not mask the data within the database itself, only when the data is presented (i.e. from the result of a SELECT statement). Therefore, the data is still vulnerable – from copying the backups or suchlike.

Dynamic Data Masking
Phil Factor – Unmasking the Dynamic Data Masking