How To Automate SQL Server Vulnerability Assessment

Powershell SQLVulnerabilityScan cmdlet is a great addition to the Powershell cmdlet libraries. Most of you know it's available through Microsoft Azure - using the interactive Advanced security pane . The powershell cmdlet - SQLVulnerabilityScan - is available through the SQL Server modules , the SMO and a bunch of new Powershell cmdlets. It's currently in Public Preview. The first step is to check that you can execute the cmdlet , point to a SQL Server and extract the report from the Vulnerability Scan. This powershell snippet - connects to a SQL Server and runs the scan over a specific database... Read more →

How to create a PSCredential object

Creating a Credential Object is useful when you require to pass a username and password through Powershell commands. You might see Credential used in various Powershell cmdlets - such as SqlVulnerabilityAssessmentScan. An example of invoking the SqlVulnerabilityAssessmentScan may be something like: Invoke-SqlVulnerabilityAssessmentScan -Credential "sa" -ServerInstance myserver\inst1 -DatabaseName "master" The problem with the way Credential is used above - will create a request for the password. What might be more efficient is to create a secure object with username \ password Enter the PSCredential. A PSCredential object represents a set of security credentials, i.e username and password. Here is an example... Read more →

How to successfully separate email in Powershell

Question: I'm trying to send an email with multiple recipients in Powershell, and getting an exception error message: Exception calling "Add" with "1" argument(s): "An invalid character was found in the mail header: ';'." When I revert to send the email to one recipient - the email is successfull . What is the correct way to pass multiple recipients for powershell email. Some more information that may assist in troubleshooting. 1) When I hardcode the email addresses in the Powershell code e.g $emailTo = "user1@org.com,user2@org.com" it works ok . 2)When I attempt to pass the values in as input parameters... Read more →

How to add SSMS to the PATH variable with Powershell

Question: I've installed the latest SQL Server Management Studio onto a server. I can find the ssms.exe through the directory - but I cannot start the SQL Server Management Studio through the cmdline - i.e type "ssms" in the command line. How can I fix this problem? Answer: I had a similar problem recently when installing SSMS 18.4. I downloaded the binary - installed without any errors - but then couldn't execute "ssms" from the command line. To solve this issue I added the directory with ssms.exe into the PATH variable and applied this as a machine wide change ,... Read more →

How to get ODBC drivers using Powershell

Question: I had a question from a developer who was troubleshooting an application to SQL Server connection via a DSN using ODBC. They wanted the SQL Server ODBC Driver names and platform (32 bit|64 bit) used to connect . They will use this information to check application compatibility . Although i can just RDP onto the server and grab the information through the ODBC gui - how can I use Powershell to get these ODBC details? Answer: You can extract the ODBC Driver name and platform via the Powershell cmdlet - Get-OdbcDriver. There are various options including to run the... Read more →