Find where Password and Username are the same

Weak security is always going to bite you.  The first thing any Hacker, Auditor, Penetration Tester or RAS is going to try is passwords that are the same as the user.

So how can we check this?  well a handy little script that uses PwdCompare, to checked the hashed password with the user name.

here it is:-

use master

select
cast(@@SERVERNAME as varchar(150)) as SQLInstanceName
,name as [LoginName]
,'Password is same as Login Name' [Description]
from syslogins
WHERE PWDCOMPARE (name,password) = 1

Powershell to do almost any thing

Below is a powershell script that will let you read servers from a table:-



CREATE TABLE [Core].[InstanceDatabases](
[DatabaseID] [bigint] IDENTITY(1,1) NOT NULL,
[InstanceID] [bigint] NOT NULL,
[DatabaseName] [varchar](50) NULL,
[RemoteDBID] [int] NULL,
[sysEffectiveFromDate] [datetime] NULL,
[sysEffectiveToDate] [datetime] NULL,
[Active] [bit] NULL,
 CONSTRAINT [PK_Instance_Databases] PRIMARY KEY CLUSTERED
(
[DatabaseID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [Core].[InstanceDatabases]  WITH CHECK ADD  CONSTRAINT [FK_InstanceDatabases_InstanceDetails] FOREIGN KEY([InstanceID])
REFERENCES [Core].[InstanceDetails] ([InstanceID])
GO

ALTER TABLE [Core].[InstanceDatabases] CHECK CONSTRAINT [FK_InstanceDatabases_InstanceDetails]
GO

ALTER TABLE [Core].[InstanceDatabases] ADD  DEFAULT (getutcdate()) FOR [sysEffectiveFromDate]
GO

ALTER TABLE [Core].[InstanceDatabases] ADD  DEFAULT ('2099-01-01') FOR [sysEffectiveToDate]
GO

ALTER TABLE [Core].[InstanceDatabases] ADD  DEFAULT ((0)) FOR [Active]
GO

In here can be stored all the servers you would like to connect to.

You will also require a few external scripts from other devs:-

http://poshcode.org/4967

http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd

You can then run any SQL on all boxes in your estate and place the answer back to a table.

Code:-


##########
Dave Dunckley
Version 1.1
Please fell free to use as your like, please post back any modifications.
############

. D:\Powershell\TSM\Plugins\invoke-sqlcmd2.ps1

. D:\Powershell\TSM\Plugins\Out-DataTable

cls

#Add-PSSnapin SqlServerCmdletSnapin100
#Add-PSSnapin SqlServerProviderSnapin100

$wmiquery = "select SystemName, Caption, DeviceID, DriveType, FileSystem, FreeSpace, Size from win32_LogicalDisk where DriveType = 3"


########################################
# Set Config Settings for this session #
########################################
#add-pssnapin sqlserverprovidersnapin100
#add-pssnapin sqlservercmdletsnapin100

################################
# Setup and populate variables #
################################

# TSM Monitoring Server
$ManagementServer="servrename"

# DBA Monitoring DB
$ManagementDB="Monitor"

# OutCSV file
$CSVOUT = 'D:\Powershell\TSM\Temp\DBLastUsed.csv'

# Query String for servers to Collect Info from
$q = "SELECT distinct upper(InstanceName) FROM $ManagementDB.Core.InstanceDetails WHERE [InstanceScan] = 1 ORDER BY 1;"

# Query Conection String
$sconn = new-object System.Data.SqlClient.SqlConnection("server=$ManagementServer;Initial Catalog=$MonitoringDB; Trusted_Connection=true");

# Open Connection
$sconn.Open()

# Open Data Adapter and create data table
$da = new-object System.Data.SqlClient.SqlDataAdapter ($q, $sconn)
$dt = new-object System.Data.DataTable

#Fill Data table
$da.fill($dt)

# Set Stage table name for Truncate
$StageTable = "DBLastUsed"

# Truncate Stage Table
Invoke-Sqlcmd –ServerInstance $ManagementServer –Database $ManagementDB –Query "truncate table stage.$StageTable"

#Loop though each server


foreach ($row.host_connection in $dt.Rows)
{
$Server = $($Row[0])
   
 
#run SQL
      invoke-sqlcmd2 -ServerInstance $Server -Database "master" -Query "
   
SELECT @@servername as servername, DatabaseName, MAX(LastAccessDate) LastAccessDate
FROM
    (SELECT
        DB_NAME(database_id) DatabaseName
        , last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update
    FROM sys.dm_db_index_usage_stats) AS PivotTable
UNPIVOT
    (LastAccessDate FOR last_user_access IN
        (last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update)
    ) AS UnpivotTable
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY 2"   |Export-CSV -NoTypeInformation $CSVOUT


#Output to temp CSV

$csvDataTable = Import-CSV -Path $CSVOUT | Out-DataTable


#Create conection

$ConnectionString = "Data Source=$ManagementServer; Database=$ManagementDB; Trusted_Connection=True;";

#Import from temp CSV directly to SQL

$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $ConnectionString
$bulkCopy.DestinationTableName = "stage.$StageTable"
$bulkCopy.WriteToServer($csvDataTable)
   



#close connections

$connSQLOUT.Open()


$cmd = $connSQLOUT.CreateCommand()
   
   
 
   
   $connSQLOUT.Close()
   
   
   


}

#enjoy!