Background:

Since our SQL SERVER are configured to be used by multiple users, some times when same username is shared by different parties, one party could lock the credential and which will fail the other party.

We can disable this feature however this will disable us the ability to actively troubleshooting and monitoring the wrong use of credential, so I will show you how to use PowerShell to get the logon details for this kind of troubleshooting.

You can run the below PowerShell on your local machine or remote server locally, I prefer run it locally to save some network traffic.

You can save the following code and run it as PS1, or simply run it line by line, I do it here line by line so you can easily play with it and get the filter right for you.

Start the Powershell and run following command:

PS > $server = "<SQLServerName>"
PS > $logs = "application"
PS > $source = "MSSQLSERVER"
#get the logs of last seven days
PS > $startdate = (get-date).adddays(-7)
PS > $exportfile = .\SQLLogRecord<ServerName>Last7Days.csv
PS > Get-EventLog -ComputerName $server -LogName $logs -after $startdate -Source $source | where {$_.message -like "*<accountNameLocked>*"} | Export-Csv $exportfile

 

#this will give you the server name to start with, then copy the csv file to your local machine and open it with Excel, only because you want to send it to Business users who caused it.

Under Excel, use “Format as Table” and Filter function to get the information you need, we can add this into the query like set eventID etc, however this KB is more about the concept and howto. Will not discuss further.

 

SQLServerLogs

About Lei

I am an IT specialist with over 10 year experience - years on Automation, on-Premise or Azure.

I am happy to develop however never want be a full time developer. Only do what I have to do. If it has to be PowerShell,HTML, PHP, CSS, C#, VBS or JS, front end or backend, so be it, doesn't matter!

Spent years with Windows, SCCM, SharePoint, SQL and Exchange servers. For last several years, I have been actively working under On Premise > Azure environment.

THERE IS NO WAY BACK!!!

Current Certificates:
    Microsoft® Certified-
  • -Enterprise Administrator
  • -Database Administrator
  • -SharePoint Administrator
  • -Administering and Deploying SCCM 2012
Red Hat Certified Technician
ITIL V3 Foundation - Practitioner

Working on Azure Certificates now and hopefully they can stop upgrading their questions one day! GIVE ME A BREAK!

Contact Lei

Name *
Email *
Comments *

Traffic since 10/08/2016

Today13
Yesterday83
This week637
This month1933
Total416293

Visitor Info

  • IP: 54.221.73.186
  • Browser: Unknown
  • Browser Version:
  • Operating System: Unknown

Who Is Online

1
Online

2017-12-17

Login