Verify access to many SQL Server instances

There may come a day where you need to verify you have access to connect to a SQL Server instance. Perhaps you need to verify this access for a hundred or maybe even a thousand servers. Doing this by hand is not great and you’re going to want to capture what you did and did not have access to. This PowerShell script will help you accomplish that.

I took the majority of the script from SQL Shack which was written by Shawn Melton, so thank you for saving me a lot of the leg work Shawn! The rest of the tweaking I did was to create files / overwrite files, append a try-catch, and make the script easily executable with variables.

To use the script, you will want to do the following:

1. Find a location to place your List.txt file. This will be a standard text file with a different server name on each line.
   1. Set your $VFile variable to the location of where you will place the List.txt file.
2. Find a location to place your FailedToConnect.txt and SucceededToConnect.txt files.
   1. Set your $VFailedLog variable to the location of where you will place the FailedToConnect.txt file.
   2. Set your $VSuccessLog variable to the location of where you will place the SucceededToConnect.txt file.
3. If you want to execute the script from the command line or PowerShell, you can also use this example to execute it:
   1. .\VerifySQLServerAccess.ps1 -VFile “c:\test\List.txt” -VFailedLog “c:\test\FailedToConnect.txt” -VSuccessLog “c:\test\SucceededToConnect.txt”
4. Let it rip!

Important notes:

This script assumes you are connecting with your domain or windows integrated account. If you need to use a SQL Authentication account, you will want to edit the script.

To edit the script for SQL Authentication, add the user account either to the outside or inside of the loop, depending on what makes sense to you.

I think it would be unlikely you have the same SQL Authentication account on every server with the same password. (But if you do… shame, shame, I know your name!) If the account name and password varies, it would be best to create another intake process for a list, similar to how we have created the $VFile variable. Below is how you can edit the script to include a username and password.

#Create connection
$uid ="test"
$pwd = "Password1"
$sqlConn = New-Object System.Data.SqlClient.SqlConnection

Now you’ll need to edit the connection string to accept the account name and password instead of integrated authentication.

Example of allowing an account name, password, and modifying the connection string:

#Create connection
$uid ="test"
$pwd = "Password1"
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = “Server=$ConnectionStringForSQL;User ID = $uid;Password = $pwd;Initial Catalog=master;Connection Timeout=1”
$sqlConn.Open()

This script assumes you are connecting with a default port for your SQL Server. If the port is not the same, you will need to append the port in your List.txt file.

Example of appending the port:

192.168.1.1,1337 vs server\instancename

Finally, here is a list of resources I found helpful when working with this script:

• https://www.sqlshack.com/connecting-powershell-to-sql-server/
• https://stackoverflow.com/questions/25682703/connect-to-sql-server-database-from-powershell
• https://stackoverflow.com/questions/5294721/how-to-specify-a-port-number-in-sql-server-connection-string
• https://www.codeproject.com/Questions/1169368/How-to-pass-list-of-value-in-powershell-script

Finally, here is the script! You can access the github to it here as well.

param(
	[string]$VFile = "c:\test\List.txt",
	[string]$VFailedLog = "c:\test\FailedToConnect.txt",   
	[string]$VSuccessLog = "c:\test\SucceededToConnect.txt"
	)

$ServerList = Get-Content $VFile -ErrorAction SilentlyContinue

Out-File -FilePath $VFailedLog
Out-File -FilePath $VSuccessLog

ForEach($computername in $ServerList)

{
	$ConnectionStringForSQL = $computername
	try{
		#Create connection
		$sqlConn = New-Object System.Data.SqlClient.SqlConnection
		$sqlConn.ConnectionString = “Server=$ConnectionStringForSQL;Integrated Security=true;Initial Catalog=master;Connection Timeout=1”
		$sqlConn.Open()

		#create command
		$sqlcmd = $sqlConn.CreateCommand()
		$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
		$sqlcmd.Connection = $sqlConn
		$query = “SELECT @@ServerName”
		$sqlcmd.CommandText = $query

		#create data adapter
		$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd

		#Create Your DataSet (and fill it)
		$data = New-Object System.Data.DataSet
		$adp.Fill($data) | Out-Null

		#Retrieving Your Data
		$data.Tables
		$computername | out-file $VSuccessLog -Append
		}

	catch{
		$exception = $_.Exception.Message
		Out-File -FilePath $VFailedLog -Append -InputObject $computername
		}
}