Tuesday, October 17, 2017

SQL Server 2012/2014: Extended Events to audit statements

Recently I had a request to set up audit for a SQL Server database to capture any DDL statements and any updates to specify tables. For this I could not run a profiler trace all the time so upon some investigation, I decided to use Extended Events to capture these events.

ADD EVENT sqlserver.sp_statement_completed(
    WHERE ((([sqlserver].[username]=N'Theusername') AND ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER TABLE%'))) AND ([sqlserver].[database_name]=N'YourDBName'))) 
ADD TARGET package0.event_file(SET filename=N'H:\MSSQL$MSSQLSERVER\AuditLogs\DDL_Changes.xel',max_file_size=(100),max_rollover_files=(10))

The above CREATE statement is to capture any ALTER TABLE commands that are executed against the database. You can explore to check how you can capture multiple types of SQL_TEXT events.

I would open the Extended Event under 'Management' in SSMS and edit the properties on this event.
You can then right click on the event and start it, to capture all the events.

To can read from the Extended event using the following commands
1) First read the xel file and write the contents to a temp table

IF OBJECT_ID('tempdb..#ExEvent') IS NOT NULL DROP TABLE #ExEvent

SELECT IDENTITY(INT,1,1) AS RowId, object_name AS event_name, CONVERT(XML,event_data) AS event_data
    INTO #ExEvent
FROM sys.fn_xe_file_target_read_file(N'H:\MSSQL$MSSQLSERVER\AuditLogs\DDL_Changes.xel', null, null, null);

2) Then read from this temporary table.

SELECT  RowId, event_name, [sql_text], [username], [database_name], [transaction_id],  [task_time]                  
    FROM (
            SELECT RowId
                    , event_name
                    , T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')AS att_name
                    , T2.Loc.query('.').value('(/action/value)[1]', 'varchar(max)')AS att_value
            FROM   #ExEvent
            CROSS APPLY event_data.nodes('/event/action') as T2(Loc)
            WHERE T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')
                IN ('sql_text', 'username', 'database_name', 'transaction_id',  'task_time')

        ) AS SourceTable
                FOR att_name IN ([sql_text], [username], [database_name], [transaction_id],  [task_time])

        ) AS PivotTable 
order by  [task_time]  desc

You can open the properties of the Event and then play with it to change some of these settings. The most important to remember how you read from the file.

Friday, October 13, 2017

PowerShell script to stop and Start SQL Server Services

As most of you know PowerShell is an amazing tool to perform a lot of Database Administration tasks.
Since last year I have been trying to create PS scripts to make my life easy. One of the tasks that I perform during our annual DR testing is stopping and starting SQL Server Services.
Before you had to login to the DB server, open the SQL Server configuration manager, change the Service Startup method (AUTO or MANUAL) and then stop/start the service. If you had SSRS, SSAS, SSIS or multiple instances then this could take time. Not to forget only the DBAs would do this.

Just like all other DBAs I am pretty lazy. That is I wanted all my tasks automated so I can work on new features and upgrade my environment. So I wrote this script to Start SQL Server Services. You will have to modify this if you want a script to STOP the services. Also, edit the Get-Service command to display the servicenames in Descending order if you are stopping the services. This is to ensure that you stop the Agent before the engine.

Note: I found a script to change the account that runs the SQL Server services from the following link. I have modifed that script to create this one.

#This script requires the hostname as the input.
Param( [Parameter(Mandatory=$True,Position=1)][string]$Server )

function PowerShell-Wait($seconds)
#This function will cause the script to wait n seconds

$host.PrivateData.VerboseBackgroundColor = "DarkMagenta"

$host.PrivateData.WarningBackgroundColor = "DarkMagenta"

$services=Get-Service -ComputerName $Server -Displayname "SQL*" | Sort-Object -Property Name
write-host "----------------------------------------------------------------"  
write-host "REMEMBER TO RUN THE SCRIPT AS ADMINISTRATOR"  -foregroundcolor "RED" -backgroundcolor "yellow"

write-host "`n Status of SQL Services" -foregroundcolor "green"
Get-Service -ComputerName $Server -Displayname "SQL*" | Sort-Object -Property Name
write-host "`n Services found:"  $services.Count 
Foreach ($servicename in $services)

if ($servicename.Name -eq "SQLWriter" -or $servicename.Displayname -eq "SQL Active Directory Helper Service") 

write-host "Skipping service : " $servicename.Displayname -foregroundcolor "RED"

$startmode = gwmi win32_service -computername $Server | where {$_.Displayname -like $servicename.Displayname} | select StartMode
if ( $startmode.startmode -eq "Disabled")
write-host "Service "$servicename.Displayname " is DISABLED so skipping service." -foregroundcolor "RED"

Write-Host "Do you want to Start the service "$servicename.Displayname" on "$Server -foregroundcolor "Yellow" -backgroundcolor "Blue"
$choice = Read-Host -Prompt '[Y/N] :'
if ($choice -eq "Y" -or $choice -eq "YES" -or $choice -eq "Yes" -or $choice -eq "yes" ) 
write-host "Starting service: "   $servicename.name -foregroundcolor "green"
write-host "    Attempting to Start de service..." -foregroundcolor "green"
Set-Service -InputObject $servicename -startuptype "Automatic" -Verbose 
Start-Service -InputObject $servicename -Verbose 
PowerShell-Wait (1)
write-host "Skipping service : " $servicename.Displayname -foregroundcolor "RED"

write-host "`n Status of SQL Services" -foregroundcolor "green"
Get-Service -ComputerName $Server -Displayname "SQL*" | Sort-Object -Property Name | Format-Table -Property Status, Name , DisplayName -auto  | Out-String

write-host "PROCESS COMPLETED"  -foregroundcolor "RED" -backgroundcolor "yellow"

Error 2601 Severity 14 State 6 Server (SYBASE) Attempt to insert duplicate key row in object 'sysusages' with unique index 'csysusages'

Number (2601) Severity (14) State (6) Server (SYBASE) Attempt to insert duplicate key row in object 'sysusages' with unique index 'csysusages'

As you might notice lately I have been writing a few blogposts related to Sybase errors. Having worked as a DB2 DBA for a long time, I do have good expertise on Linux/UNIX servers. So in my current environment I am the Sybase DBA because the database features is very close to SQL Server.

Anyways, for the last two weeks I have been building a new Sybase server and hence all these errors and posts about them.

Today I was trying to add a new device to the database and alter the database. But I got the above error.
Along with that when I try to find the database size using a query against sysdatabases and sysusages, I get the following error.

And I could not extend the existing database devices. After a lot of troubleshooting I executed the following and fixed my database device issue.

sp_dbremap dbname

Thursday, October 12, 2017

Sybase : Volume validation error: bad magic number EOF1, expected USTH.

Oct 11 22:36:58 2017: Backup Server: Volume validation error: bad magic number EOF1, expected USTH.

Oct 11 22:36:58 2017: Backup Server: compress::/sybase/dump/Database.20171011.14.DB::013: volume not valid or not requested (server: , session id: 72.)

Currently, I am working on migrating a Sybase server to a new host and I installed a higher version of Sybase on that host. When I tried to LOAD the Database DUMP from the current server I got the above error.

All the help that I got online pointed towards the cause being that the version where I took the backup was at a higher level than where I was loading to. But that was not the case.

Then I started working on a uncompressed dump file that seemed to do the trick. However, the production database was so large that I didn't have enough disk space to take an uncompressed dump file.
So next I tried to use the native option for compression in the LOAD command

dump database mydb "/sybase/dump/database.dmp"
with compression = "9"

This seemed to do the trick. I had actually dumped to multiple stripes so I had to modify the above command.

To test this further I tried it without the compression option that too seemed to work.
So try this too

dump database mydb "/sybase/dump/database.dmp"

Friday, October 6, 2017

Sybase: Change SA password

Sometimes, when DBAs don't follow proper documentation they end up in situations where they don't know the SA password. This has to be the most fatal mistake for a DBA. Fortunately, in Sybase there is a way to change SA password if you don't have it.

1.      Stop the Sybase Server.
2.      Locate the RUN_SPS_XXXXXX file. It is typically located in the $SYBASE/ ASE-15_0/INSTALL/ directory.
3.      Open the RUN_SPS_XXXXXX file.
4.      Add the following command line option to the end of the command line  “ -psa”

Note: This option is case sensitive, should be lowercase, and entered without quotes.
New SSO password for sa:XXXXXXXXXXXXXX
Note: You may have to scroll up to find this line.

5.      Run startserver -f RUN_SPS_XXXXXX to start the Sybase Server. Startup messages you would normally see in the errorlog will be printed to the screen.
6.      After a few minutes, when the startup messages have stopped printing to the screen, look for a line similar to the following within the startup messages:
7.      Attempt to log into the Sybase Server using Sybase Central or SQL Advantage with the new password. You should be able to login without any errors. If not, please contact the SPS Help Desk.
8.      Once you are logged in, update the password for the sa account.
9.      Logout of Sybase Central or SQL Advantage.
10.    Shutdown the Sybase Server.
11.    Edit the RUN_SPS_XXXXXX again to remove the -psa option then save it.
12.    Restart the Sybase Server. 

Wednesday, April 19, 2017

Error: 18456, Severity: 14, State 38

Login failed for user [User]
Error: 18456, Severity: 14, State 38

I have seen this error multiple times and typically it involves creating a user for that login.
However, today I noticed this on a new server that I configured as a Disaster Recovery.

State 38 means 'Login valid but database unavailable (or login not permissioned)'.
I noticed that on production this account was listed as the DB OWNER since this was used to create the database. Hence, I didn't have to explicitly create the user in the database.

However, on the DR server I had to create this user in the database and grant it dbowner.
When we failed back we had the same error so I was puzzled. Since this account was working fine before we failed over to DR. So there was no way it would not work after we crawl back to PROD.

I noticed that there are multiple ways I can fix this error.

1) Create the user and grant dbowner to the user.
2) Else execute the sp_changedbowner and make this use the owner. This will give implicit permissions on the database.

Error: 17806, Severity: 20, State: 2.

Error: 17806, Severity: 20, State: 2.
SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: xxx.xxx.x.x]

Error: 18452, Severity: 14, State: 1.
Login failed for user ”. The user is not associated with a trusted SQL Server connection. [CLIENT: xxx.xxx.x.x]

SQL Server 2012/2014

I have seen several types of login errors but this one seems to be a confusing one. Today while trying to connect from a third party I noticed that I had three options for Authentication

  • SQL Server Authentication
  • Windows Authentication
  • NTLM2 Windows Authentication

When I tried using just 'Windows Authentication' I got the above error, so I created the login explicitly and it worked. But then I tried to use the 'NTLM2 Windows Authentication' the login was successful. To confirm this I ran the below query to confirm that SQL Server was using NTLM authentication. I was connected from a client when I ran this query.

SELECT DISTINCT auth_scheme FROM sys.dm_exec_connections

So lesson for today is to use NTLM authentication when you get this error. However, I am going to investigate what client I can use which has this authentication option.