Feeds:
Posts
Comments

Archive for June, 2014

A few months ago, we put in more layers of monitors on our production database servers. One such improvement is based on logins. Both OS logins and Sql Server logins. On any given regular day, not many people log into production servers (OS – login); May be 1 or 2 in a day. So, it will be good monitor if anyone logs in and causes repeated login failures. Similarly for database servers.

For database servers, we monitor all login failures —  as there should not be any DB login failures. If repeated failures occur that it is an indication of a problem. Either unauthorized principal attempt or DDoS attack or authorized principal attempt. Either way it needs to be monitored.

With all this extra monitoring, we’ve been receiving a lot of login alerts. But most of them are false positives — like Password Expiration, Password reset, User account locked, etc. After getting too many of these, we started looking into them. A typical alert message would look like this:

Monitor:                 SQLApplicationLogMonitors: DB-SVR1 -- Security Login Failures with ID 4776
Group:                   SQL Application Log Monitors
Status:                  Microsoft-Windows-Security-Auditing:4776 on DB-SVR1, 
Category:                14336
Message:                 The computer attempted to validate the credentials for an  account.
Authentication Package:  MICROSOFT_AUTHENTICATION_PACKAGE_V1_0
Logon Account:           JohnDoe
Source Workstation:      DB-SVR1
Error Code:              0xc0000071
Matched on:              Event: Microsoft-Windows-Security-Auditing:4776
Type:                    Error
Timestamp:               18:29:19 01/10/14
Sample #:                35013

Upon looking into this further, we understood that these false positives errors are not errors, but log entries indicated the nature of action under the category called “security” — we were monitoring everything under this category.

Following table shows, what each of these Error Codes mean:

Error Code:

Error Code

Error Description

C0000064

user name does not exist

C000006A

user name is correct but the password is wrong

C0000234

user is currently locked out

C0000072

account is currently disabled

C000006F

user tried to logon outside his day of week or time of day restrictions

C0000070

workstation restriction

C0000193

account expiration

C0000071

expired password

C0000224

user is required to change password at next logon

C0000225

evidently a bug in Windows and not a risk

Equipped with this better understanding we were able to configure the monitors to only look for real errors and not informational.

Hat tip to my colleague Bill for teaching me this.

Hope this helps,
_SqlTimes

Advertisements

Read Full Post »

Quick one today:

Sometimes, during troubleshooting, PRINT commands come very handy in traversing the progress of procedure (or any batch of SQL code) execution.

When you have a large amount of code with hundreds of of lines of code, you might end up with many PRINT statements capturing the status.

The result might end up looking like this:

--
--  Storing procedure execution progress in a status variable for troubleshooting
--
DECLARE @ProgressReport VARCHAR(8000)

SELECT @ProgressReport = '1 : Start : ' + CONVERT(VARCHAR, GETDATE(), 109)
SELECT @ProgressReport = @ProgressReport + '2 : @SV_Stats : ' + CONVERT(VARCHAR, GETDATE(), 109)
SELECT @ProgressReport = @ProgressReport + '3 : Starting While : ' + CONVERT(VARCHAR, GETDATE(), 109)
SELECT @ProgressReport = @ProgressReport + '@SVP_ID : ' + CONVERT(VARCHAR, 4654)
SELECT @ProgressReport = @ProgressReport + '@SwitchOut_PartitionNumber : ' + CONVERT(VARCHAR, 54)
SELECT @ProgressReport = @ProgressReport + '4 : Switch completed : ' + CONVERT(VARCHAR, GETDATE(), 109)
SELECT @ProgressReport = @ProgressReport + '5 : Merge Completed : ' + CONVERT(VARCHAR, GETDATE(), 109)
SELECT @ProgressReport = @ProgressReport + '6 : While Completed : ' + CONVERT(VARCHAR, GETDATE(), 109)

PRINT @ProgressReport
GO

Non user friendly result

The result looks like this (not user friendly):

NewLine CarriageReturn Before

NewLine CarriageReturn Before

So, to make it pretty, or more readable, ‘new line’ & ‘carriage return’ characters come handy.

User friendly format

--
--  Storing procedure execution progress in a status variable for troubleshooting with CHAR(13) + CHAR(10)
--

DECLARE @ProgressReport VARCHAR(8000)

SELECT @ProgressReport = '1 : Start : ' + CONVERT(VARCHAR, GETDATE(), 109) + CHAR(13) + CHAR(10)
SELECT @ProgressReport = @ProgressReport + '2 : @SV_Stats : ' + CONVERT(VARCHAR, GETDATE(), 109) + CHAR(13) + CHAR(10)
SELECT @ProgressReport = @ProgressReport + '3 : Starting While : ' + CONVERT(VARCHAR, GETDATE(), 109) + CHAR(13) + CHAR(10)
SELECT @ProgressReport = @ProgressReport + CHAR(9) + '@SVP_ID : ' + CONVERT(VARCHAR, 4654) + CHAR(13) + CHAR(10)
SELECT @ProgressReport = @ProgressReport + CHAR(9) + '@SwitchOut_PartitionNumber : ' + CONVERT(VARCHAR, 54) + CHAR(13) + CHAR(10)
SELECT @ProgressReport = @ProgressReport + CHAR(9) + '4 : Switch completed : ' + CONVERT(VARCHAR, GETDATE(), 109) + CHAR(13) + CHAR(10)
SELECT @ProgressReport = @ProgressReport + CHAR(9) + '5 : Merge Completed : ' + CONVERT(VARCHAR, GETDATE(), 109) + CHAR(13) + CHAR(10)
SELECT @ProgressReport = @ProgressReport + '6 : While Completed : ' + CONVERT(VARCHAR, GETDATE(), 109) + CHAR(13) + CHAR(10)

PRINT @ProgressReport 
GO
NewLine CarriageReturn After

NewLine CarriageReturn After

 

With CHAR(13) + CHAR(10) the result looks more user-friendly; Looks more like the actual code and its status at every step. Saves a lot of time in troubleshooting. From MSDNCHAR can be used to insert control characters into character strings. The following table shows some frequently used control characters.”

Control character

Value

Tab char(9)
Line feed char(10)
Carriage return char(13)

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Again today, I ran into another interesting error message.

In our lab environment, playing with one of our customer databases, I ran into this interesting error.

Msg 1833, Level 16, State 2, Line 5
File 'filename' cannot be reused until after the next BACKUP LOG operation. If the 
database is participating in an availability group, a dropped file can be reused 
only after the truncation LSN of the primary availability replica has passed the 
drop LSN of the file and a subsequent BACKUP LOG operation has completed.

Essentially, it is saying:

  • The same file name (ndf file) cannot be reused until BACKUP LOG is performed
  • If you have Availability Groups configured, same file name cannot be re-created, until the “remove file” change has successfully synchronized to the replicas.

 

This is how I ran into this issue:

Step 1: Create a new FG and File

--
-- Create a filegroup
--
ALTER DATABASE SampleDB
ADD FILEGROUP [SV_FG]
GO

--
-- Add a data file to it
--
ALTER DATABASE SampleDB
ADD FILE (
     NAME      =   N'NewDataFile'
   , FILENAME  =   N'I:\MSSQL\Data\NewDataFile.NDF'
   , SIZE      =   2 GB
   , FILEGROWTH=   10%
)
TO FILEGROUP [SV_FG]
GO

Step 2: DROP the FG and data file

--
-- Now drop the file and its associated data file
--
ALTER DATABASE SampleDB
REMOVE FILE NewDataFile
GO

ALTER DATABASE SampleDB
REMOVE FILEGROUP [SV_FG]
GO

Step 3: Recreate same FG and data file

--
-- Now re-create the same filegroup and file.
-- FG runs successfully, but when you create the file, it throws an error message
--

--
-- Create a filegroup
--
ALTER DATABASE SampleDB
ADD FILEGROUP [SV_FG]
GO

--
-- Add a data file to it
--
ALTER DATABASE SampleDB
ADD FILE (
NAME = N'NewDataFile'
, FILENAME = N'I:\MSSQL\Data\NewDataFile.NDF'
, SIZE = 2 GB
, FILEGROWTH= 10%
)
TO FILEGROUP [SV_FG]
GO

Resolution

Creating FG runs successfully, but creating the data file throws an error. Following the instructions in the error message, once I performed transactional log backup, a new data file (.ndf) was successfully created with the same old name.

--
-- Perform transactionallog backup
--
BACKUP LOG SampleDB
TO DISK = N'M:\MSSQL\Backup\MSSQLSERVER\SampleDB.TRN'
GO

Now, when the same script to create new filegroup and file are executed, it runs successfully.

--
-- Add a data file to it
--
ALTER DATABASE SampleDB
ADD FILE (
     NAME      =   N'NewDataFile'
   , FILENAME  =   N'I:\MSSQL\Data\NewDataFile.NDF'
   , SIZE      =   2 GB
   , FILEGROWTH=   10%
)
TO FILEGROUP [SV_FG]
GO

Conclusions

So, why does Sql Server care that we take a transactional backup when a data file is dropped and being re-created. My guess is:

  • The “REMOVE FILE” change must be synchronized to the replicas before a new file with same name is created.
  • Sql Server wants to secure the changes. Since file level changes are not the same as table or function or other objects, may be Sql Server want to secure these into a safe backup file, then move on to making more changes. If you have a better or correct explanation, please share in the comments.

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Sometimes on new machines, that have Sql Server installed, I check to make sure if anyone is listenning on port 1433. By default, Sql Server listens on port 1433 for incoming connection requests over TCP/IP protocol. Sql also lstens on port 1434 fo DAC, for that is for a later post.

Also, when you are troubleshooting issues like in this previous post.

How to check if a particular port is actually active:

use netstat -an

It is a common and powerful to see what ports are open and have a listener on and what connections are established over what ports.

See sample report below:

NetStat Open Ports

NetStat Open Ports

 

With this you can see what ports are open and have a listener on. It helps with troubleshooting.

Hope this helps,
_SqlTimes

Read Full Post »

Quick one today:

Ran into an interesting error message today. On one of our lab servers, I was making some changes to test a deployment and I was running FULL database backup and adding/removing some data files on the same database.

Boom !! I got this error.

Msg 3023, Level 16, State 2, Line 1
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
--
--  Run backup job
--
BACKUP DATABASE SampleDB
TO DISK = N'M:\MSSQL\Backup\MSSQLSERVER\XS and RT\test.BAK'
WITH COMPRESSION
GO

--
--  While backup is running, I attempted to create a new data file.
--
ALTER DATABASE SampleDB
ADD FILE 
(
    NAME        = N'StoredValue_FG'
  , FILENAME    = N'I:\MSSQL\Data\MSSQLSERVER\StoredValue_FG\StoredValue_FG.NDF'
  , SIZE        = 100 MB
  , FILEGROWTH  = 10%
)
TO FILEGROUP [SV_FG]
GO

The message is very self descriptive. Essentially it boils down to: “Do not run any file manipulations, when running database backup“. Which makes sense. Looks like when database backup runs, Sql Server does not allow data files changes to the same database. Since database backup is responsible for copying (backingup) data in all the data files into a backup file, it may want to restrict and changes to data files.

After the backup completed, the data file changes are successful.

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

In our lab, we have a lot of Sql Server Developer Edition software installed. For the most part, for these Sql Instances, we do not expect any external connections to come in. But once in a while we need them to be available for external connections. That is where we run into human error.

We get errors like this:

TITLE: Connect to Server
------------------------------
Cannot connect to 153.60.88.142
------------------------------
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection 
to SQL Server. The server was not found or was not accessible. Verify that the instance 
name is correct and that SQL Server is configured to allow remote connections. 
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) 
(Microsoft SQL Server, Error: 5)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=5&LinkId=20476
------------------------------
Access is denied
------------------------------
BUTTONS:
OK
------------------------------

Usually, we check to make sure

  • Port 1433 & 1434 are open in Firewall
  • Check the box in Instance properties >> Connection >> “Allow Remote Connections to this server”
  • But for default Editions there is one more point to check, i.e. TCP/IP properties under “Sql Server Network Configuration”

When you connect from the same server, Sql uses “Shared Memory” to fulfill the connection requests. But any connections coming in from outside the machine, need to go through “TCP/IP” or “Named Pipes”. So make sure they are enabled.

See the image below:

TCP/IP is Disabled, by default

TCP/IP is Disabled, by default

Hope this helps,
_SqlTimes

Read Full Post »