How to check Enforce password expiration is set for SQL Login

Question: We had an unexpected application outage due to a SQL Login which had Enforce password expiration set . Unfortunately this was not being monitored, and led to a situation where the password had expired and the application was able to logon onto the database. It's the sql server team security policy to not have SQL Logins set with MUST_CHANGE ON. Part of enforcing and monitoring the policy includes incorporating certain checks in daily sql server security violations reports. What is the sql code to execute this check and to identify if any SQL Logins have Enforce Password Policy and... Read more →

How to fix Powershell Execution Timeout Expired with CommandTimeout

Question: I'm getting this Execution timeout error when executing a Powershell script using the $sql.ExecuteReader() method. How can I fix this issue? Exception calling "ExecuteReader" with "0" argument(s): "Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding." Answer: To overcome this issue - use the CommandTimeout parameter and set to a value of 0. This will make it infinite. So it would be something like : $cn = new-object System.Data.SqlClient.SqlConnection "server=myInstance;database=master;Integrated Security=sspi" $cn.Open() $cn.CreateCommand() $sql.CommandText $sql.CommandTimeout=0 $sql.CommandTimeout=0 $sql.ExecuteReader() Read More on Powerhsell and connecting to SQL Server Powershell - run... Read more →

Powershell script - Get the total table count of all databases in multiple SQL Server Instances

To get the total table count of all databases in either one or more SQL Server Instances use this powershell script. This basic powershell script will give you the basic framework - which you adapt for your own purposes. For example - you may want to store this in a report or capture and place in some sort of datawarehouse. This script - similar to How to get SQL Server database object count using Powershell Measure-Object - iterates through a list of SQL Servers - listed in the file referenced in the $instancepath. The results of the command executed are... Read more →

How to get SQL Server database object count using Powershell Measure-Object

Question: How can I iterate through a list of SQL Servers , extract the count of the databases , place into a powershell data table and get the database count sum for all the servers? I'm having some trouble thinking about how to add up the counts from the individual servers into a total count , using powershell functions. Answer: This basic powershell script will give you the basic framework. This script iterates through a list of SQL Servers - listed in the file referenced in the $instancepath. The results of the command executed are added to the DataTable .... Read more →

Powershell script for unattended install of SQL Server standard patches

Question: Do you have a powershell script allowing me to dynamically create a drive - pointing to a network share , and then start an unattended install of SQL Server patching? I'll afdapt the script to drop onto a server and execute according to schedule . Answer: Here is an example of powershell script that applies SQL Server 2016 SP2 CU7 , although you can apply the same framework for other patching . Some key points to note: 1) Replace "my_path" with the network path . The drive is added by use of the PS-Drive cmdlet. If you want to... Read more →

How to check the SQL sa Login is disabled

Often organisations have a SQL server security policy dictating the 'sa' login is disabled. There is some sound reasoning behind this policy. The primary reason is to decreases the surface area available to attack - and is a common principle of security. When this is a requirement - I'll add some code to disable 'sa' as part of the build and certification process. But , from time to time - the 'sa' login will be re enabled, so it's important to build an alerting or reporting mechanism identifying the exception. The sql statement to check if 'sa' is disabled. SELECT... Read more →

DELETE FROM OPENQUERY

Question: How can I DELETE using OPENQUERY? Normally for a SELECT from OPENQUERY , I'll do something like : SELECT col1 ,col2 FROM OPENQUERY (MY_LINKED_SERVER,'SELECT col1,col2 FROM MY_LINKED_TABLE') Can a similar method be used but for DELETE? Answer: To use DELETE , it's a similar process to the SELECT but using the DELETE syntax. Taking the SELECT example above - you'll do the following. Note:this example will delete ALL the rows in the linked server table . : DELETE FROM OPENQUERY (MY_LINKED_SERVER,'SELECT col1,col2 FROM MY_LINKED_TABLE') Read more on OPENQUERY,Linked Servers and AS400 SQL Server – Datetime date range , AS400... Read more →