Home » SQL Server Security

SQL Server Security

sql server DBA 999

Microsoft SQL Server Error: SQL SERVER – Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ‘)’ or msg 102 level 15 state 1 line 3 incorrect syntax near ‘ ‘

Microsoft SQL Server Error: SQL SERVER – Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ‘)’ or msg 102 level 15 state 1 line 3 incorrect syntax near ‘ ‘

Details of Error:

This error message occurs if we use GETDATE() like function as a parameter to Stored Procedure or Function Statement. or the second error is pertaining to problem when inserting data can be solved by making the column not null and also can be made default to some value.

Actual error message:

Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ‘)’.’

Resolution:

This problem can be solved by using a local variable … Read the rest

sql server DBA 999

Microsoft SQL Server Error: SQL SERVER – Msg 8152, Level 16, State 14 – String or Binary Data Would be Truncated

Microsoft SQL Server Error: SQL SERVER – Msg 8152, Level 16, State 14 – String or Binary Data Would be Truncated

Details of Error:

String or binary data would be truncated. The statement has been terminated.

This error message below occurs when part data is been truncated while loading a table.

Actual error message:

Msg 8152, Level 16, State 14, Line 8
String or binary data would be truncated.
The statement has been terminated.

Resolution:

Either Shortened the data which is being truncated or increase length of the column on the Table or use below statements at the top and bottom of sql insert statement which is suppress the error

SET ANSI_WARNINGS OFF;
-- Insert
Read the rest

sql server DBA 999

Microsoft SQL Server Error: 53 or Microsoft SQL Server Error: 11001 or SQL Server error 53 and 40. SQL server error 53 and 17

Microsoft SQL Server Error: 53 or Microsoft SQL Server Error: 11001 or or SQL Server error 53 and 40. SQL server error 53 and 17

Details of Error:

Can not connect to SQL Server error which can be Error Number: 11001 or Error Number:53, Severity: 20, State: 0 or SQL Server error 53 and 40. SQL server error 53 and 17

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.

Connection failed
SQL State '01000'
SQL Server error 53
[microsoft][ODBC SQL Server Driver][DBNETLIB]Connection Open
Connection failed
SQL State '08001
SQL
Read the rest

sql server DBA 999

Protecting your database using Transparent Data Encryption in SQL Server 2008

Encryption:
Encryption is the process of transforming information in plain text using a cipher, or algorithm, to make it unreadable to everyone other than the person who has the key. There are two types of keys; symmetric and asymmetric. When the same value is used to encrypt and decrypt, then it is known as a symmetric key. An asymmetric key has two parts: one is a private key and the other is a public key. The private key is used to encrypt the data and the public key is used to decrypt the data.

TDE: is a full database level encryption that protects the data files and log files.
As per Microsoft documentation for TDE

“Transparent data encryption (TDE) performs
Read the rest

sql server DBA 999

SQL Server RPO and RTO

Recovery Point Objective(RPO) describes a point in time that data can be restored from. For instance, if there is data corruption, Data loss or unavailability, at what point in time can a valid copy of the data be restored from? RPO is usually measured as minutes, hours, days, weeks, etc… for Business Continuity.

If a database is corrupted then can we restore it back to what it was 15 minutes ago? This would be an RPO of 15 minutes for Business Continuity..

Recovery Time Objective (RTO) is the amount of time which data or hardware is desired to be restored after a data corruption or hardware failure.

If a Database server just died. Time needed to bring it up and … Read the rest

sql server DBA 999

ASYNC_NETWORK_IO Waits SQL Server 2005 2008 2012 2014 2016

ASYNC_NETWORK_IO Waits SQL Server 2005 2008 2012 2014 2016

ASYNC_NETWORK_IO Wait type in SQL 2005 2008 2012 2014 or 2016 are associated with either a Network Performance issues in the Environment or a the client application is not processing results quickly enough from SQL Server.

As always, if you are One of the DBA’s who check Top Waits Stats on SQL Servers to get a insight on what is SQL Server waiting on actually for this performance issues. Below few tips on troubleshooting ASYNC_NETWORK_IO Waits SQL Server

The Top Waits Stats rollup query is used to find the Top Waits SQL Server is waiting on from last restart of SQL Server.

The First thing when dealing with ASYNC_NETWORK_IO Wait types … Read the rest

sql server DBA 999

SQL Server 2008 Authentication modes

Before we go into SQL Server 2008 Authentication modes, it is better to have quick look at Active Directory Services.
Active Directory (AD) is a directory service created by Microsoft for Windows domain networks. It is included in most Windows Server operating systems. Server computers on which Active Directory is running are called domain controllers. There can be more than one Domain Controllers in a Organization which are replicated internally. Active Directory serves as a central location for network administration and security. It is responsible for authenticating and authorizing all users and computers within a network of Windows domain type, assigning and enforcing security policies for all computers in a network and installing or updating software on network computers.… Read the rest

sql server DBA 999

Changing the Service Accounts Used by SQL Services

Firstly its good to know the types of startup accounts:

Local User Account: This user account is created in your server where SQL Server is installed, this account does not have access to network resources.

Local Service Account: This is a builtin windows account that is available for configuring services in windows. This account has limited access permissions to the resources in the server.

Local System Account: This is a builtin windows account that is available for configuring services in windows. This account is a high privileged account that has access to all resources in the server with administrator rights.

Network Service Account: This is a builtin windows account that is available for configuring services in windows. This account has … Read the rest