Hide UserName and Password with SQLCMD

SQLCMD is a great tool. We have certain scripts that uses sqlcmd .The best way to connect to SQL Server database using sqlcmd would be to use Windows Authentication (i.e.; with –E switch).Unfortunately Windows Authentication won’t work everywhere in enterprise when you have distributed databases running in different domain and under different RDBMS.So we had to use SQL Server Authentication and the username and password were hardcoded with sqlcmd.

One of the concern security team had was the username and password are hardcoded in the bat file which execute sqlcmd script and that should be avoided. Application normally saves their setting in registry and then gets the information from there. We were also looking for a way on how to do this with SQLCMD.

One of the article from MSDN which I found very helpful was

http://msdn.microsoft.com/en-us/library/ms188714.aspx

This had reference of setting environmental variables and then using it with SQLCMD.Specifcally the note was

If more than one type of variable has the same name, the variable with the highest precedence is used.

1.       System level environmental variables

2.       User level environmental variables

3.       Command shell (SET X=Y) set at command prompt before starting sqlcmd

4.       sqlcmd -v X=Y

5.       :Setvar X Y

SQLCMD gives variables which can be defined like SQLCMDUSER, SQLCMDPASSWORD, SQLCMDINI etc. The msdn  article has complete list of variables which can be set in environmental variables.

Lets start with connecting to SQL instance with SQLCMD.The screenshot shows the default connection which is Windows Authentication

Lets verify the username which sqlcmd uses to connect.The function SUSER_NAME() will return current user.

So SQLCMD has used windows authentication and  an inbuilt user account to connect to SQL Instance.

Now lets create a sql authenticated id "testsqlcmduser"  in SQL Server instance.

Please note I have selected  to enforce password policy though its not needed but still its a good practice to enforce password policy in sql authenticated id.

Now lets add this user  and its password in system environmental variable.

Go to Control Panel->System->Advanced setting and then click on Environment Variables.You would get a screen like this

You can add the variable in System Profile or current user profile.Click on new button and add the variable SQLCMDUSER and its value as shown below

Similarly add the SQLCMDPASSWORD variable

Click on OK and Apply button.The environmental variables ar all set.Now lets use it with SQLCMD.
Go to command prompt and type sqlcmd  and give the  SQL Instance Name.

As shown in screenshot above it will get connected .Now lets verify what is the userid which sqlcmd used to connect to SQL Server instance

It has used testsqlcmduser which is the sql authenticated id.How did it worked.We have not even passed the userid and password.Now when you miss -U and -P option sqlcmd checks the system and user profile variable to see if any of the sqlcmd variables are set.If so it uses that to connect to SQL instance.If there are none then it uses windows authentiction to connect to SQL Server instance.

Now lets execute some TSQL command with sqlcmd. I have created a small script  file with the following TSQL

use Encryption_Test;
select * from dbo.EncryptedCustomer;
select SUSER_NAME();

Basically it connect to a database Encryption_Test in my local sql instance which was created to test encryption in SQL Server 2008.The script then executes a select command on a table in that database which has some of the encrypted data.The last line returns the current connected user.

The result is shown below

Please note we have not specified  username or password.So we are hiding the details which are needed to connect to SQL Server instance.Even we can hide the SQL instance name by adding the value in variable SQLCMDSERVER.

Query timeout expired error in ASP application

Timeouts are common in any enterprise level application. With the huge size of database and concurrent users accessing application I am sure most of you might have encountered timeouts in application.

There can be many reasons for timeouts. The prominent ones are blocking or CPU/memory usage going high. Recently I had to work on application that was getting frequent timeout error.The error is shown here

             

On enquiring the details of application I was informed that it was an old ASP application and the database was running in SQL Server 2005.There is a particular report which when accessed throws the timeout error. A specific stored procedure was executed for this report and the parameter was date range supplied.

Like any DBA who has some knowledge on application programming and administration I thought of recommending them to increase the connection/command timeout value in connection string or IIS.But then a question flashed “Is this the best way to handle this issue?”.

The answer was No. Ideally I should monitor the instance for any blocking. If there are no blockings then check the CPU and RAM utilization. Execute a profiler and capture the statements utilizing high CPU and tune them and check if still you get timeouts or not. If still the issue persists then request network admin to start a netmon trace to check if data-packets across network are getting lost. 

On checking the instance for blocking (blocked processes) I couldn’t find anything. There was one more application on which I was closely working that also has the same issue of timeout but it  was predominantly due to processes blocking each other. But in this instance I couldn’t find any process that was blocked. Now the next doubt was CPU or memory usage.

To monitor the statements consuming high CPU I decided to start a trace using profiler. Please make sure that whenever working on application related timeout issue the event Attention in profiler is selected. This event would get fired whenever application gets a timeout. You can then check which TSQL statement was executed just prior to this timeout. The events selected in profiler were:

Attention

User Error Message

RPC: Starting

RPC: Completed

SP:Starting

SP:Completed

SP:StmtStarting

SP:StmtCompleted

SQL:BatchStarting

SQL:BatchCompleted 

On running the trace what I found was certainly the CPU time, reads and duration for the particular SP was high and I was able to locate the exact TSQL statement which was throwing timeout.

As shown in the screenshot the update statement was creating issue. The reads were high and it was consuming high CPU cycles. This update was executed inside a while loop and the same trace file  had details about the reads and CPU of previously completed update statements.

Now the next part was to check the execution plan of this update statement and create appropriate indexes. I was able to find a scan on a table that was having 4313995 records. Suggested them to create non clustered indexes on this table so that the table scan was replaced by a seek. Once the indexes were created the timeout errors were resolved. On checking the trace again what I found were the CPU time was drastically reduced.

This is one of the reasons for getting timeout errors. I am sure there are many other factors some of them I have explained before (blockings, network issue etc).Normally application developer thinks that these timeout are issues related to database but what I have seen is they are due to bad TSQL code or database/application design.

Nested Loop vs Hash Match

Last week I was called to help a team facing performance issue in one of the production database instance. This was the statement

Our application performance has good overall past 2 weeks, however today we notice that this one single SP is taking 50 secs to execute when the same took 2 secs yesterday .


The issue was with single stored procedure. The first thing to check was if there is any blocking in the database instance. No, nothing!!!. I couldn’t find any blocked processes in the instance.


Since the issue was with single stored procedure I started to perform the steps which every DBA will do.Took the list of tables on which this stored procedure is dependent on and checked their fragmentation details. No the tables were not fragmented.


OK then maybe there would be issue with statistics of these tables. Probably the statistics of tables were not updated. But when I checked the statistics were fine.


At this point I decided that a trace needs to be started to capture the execution plan of the queries. On  analyzing the execution plan I found that a particular select statement doing join on huge tables was using Nested Loop join. I remember the lines from a book written by Grant Fritchey that nested loop join are appropriate for small tables and for large tables a hash match join would be a better join.

Whoop is this the problem. Is optimizer creating a wrong plan? If so why. Do I need to give join hints here and see if optimizer generates a better plan? I was thinking about these all things then suddenly a though flashed across my mind. Why don’t flush the plan cache and see. I use the command
 DBCC FLUSHPROCINDB to flush all the procedure cache for the particular database .



Once the plan cache was flushed the stored procedure was behaving as expected. It was responding fast. Now I decided to capture trace with execution plan once again  to check if the nested loop join operator was changed or not. As expected the query was using hash match join and not nested loop.

Probably updating the statistics would have resolved the issue but the tables were huge and updating the statistics would have taken more than 20 minutes. I could have tagged the procedure cache to be recompiled by using sp_recompile stored procedure but at that time flushing the procedure cache looked a better option. The changed execution plan is given below.

   
Nested loop join works better if the tables joined are small.As long as you have small dataset being joined this is the best join that you would expect to see.

Hash Match joins are often very effecient on large datasets specially if one table is much smaller than the other.It works well  on tables which are not sorted on columns used in join condition.Be aware that sometimes it may also be an indication of a missing where clause or an of an index.

The KB article has the details on how the three join operators normally work
http://support.microsoft.com/kb/197297

Disabled jobs run automatically in SQL Server 2005/2008.

A common myth  is that there is a bug in SQL Server 2005/2008 that a disabled job will run automatically in  SQL Server unless its schedule is disabled. I was also thinking in the same way  till today this is when I decided to test the same.

The first step was to create a SQL Server Test job that executes sp_who2 in its step. The schedule of the job was run daily every minute. As usual job was running every minute.

Now decided to update the status of job in sysjobs  table in msdb.I disabled it using the query

update sysjobs set enabled=0 where name='Test_SQLBugJob'

The job was disabled as shown in the screenshot.

But to when I noted the job history what I found was the job was still executing.

The next thing to do was to update sysjobactivity table since it has a column next_scheduled_run_date so if I can make it null probably the job won’t execute.The query  given below was executed.

update sysjobactivity set next_scheduled_run_date=null where job_id=(select job_id from sysjobs where name='Test_SQLBugJob')

But still after the job was executing as per schedule. Though the next_scheduled_run_date was null but the job will continue running and  once its executed it will update the next_scheduled_run_date column with a new value.

Decided to give a try with SQL Server Management Studio. When disabled through SQL Server management studio things were good and the job never ran. Now what  commands does SQL Server management studio executes.

Started a profiler and captured the commands. Found that Management studio executes a command

sp_update_job.This stored proc in turn executes  another stored procedure sp_sqlagent_notify

which again executes an extended stored procedure xp_sqlagent_notify which is in SQLServer library file xpstar.dll.

SQLServer agent caches the data in memory.Unless  sp_aqlagent_notify is executed the cache is not refreshed.When you  use SSMS then the command gets executed and SQL Server Agent refreshes its cache will the new setting.

One of my friend  pointed out that the bug is logged in Microsoft lab

https://connect.microsoft.com/SQLServer/feedback/details/552707/job-disabled-and-schedule-enabled#details

But if the workaround is to restart the SQL Server agent which means its refreshing the cache.So the point here is if you want to disable a SQL Server agent job never execute plain TSQL update statement.Either you use SSMS or execute the stored proc sp_update_job.

 

SQL Server could not spawn FRunCM thread

Today got an annoying error with SQL Server 2008.The error when trying to start SQL Server service was

SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

This error was logged in application log of Windows Event viewer.On checking the Widows system log the error was

The SQL Server (SEREENASQL2008) service terminated with service-specific error %%-2146885628.

Tried changing the startup account to LocalSystem account but it didn't help to fix the issue.

The error logged in both windows event viewer log also was not quite helpful.

At this stage I decided to check SQL Server error log which was in the path C:\Program Files\Microsoft SQL Server\MSSQL10.SEREENASQL2008\MSSQL\Log.

The SQL Server error log has an entry like

2011-02-18 22:57:43.43 spid15s     Clearing tempdb database.
2011-02-18 22:57:43.62 Server      Error: 26014, Severity: 16, State: 1.
2011-02-18 22:57:43.62 Server      Unable to load user-specified certificate [Cert Hash(sha1) "BCF96EB3346FFC3580D3D833B7C39D1021D88152"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.
2011-02-18 22:57:43.62 Server      Error: 17182, Severity: 16, State: 1.
2011-02-18 22:57:43.62 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.

Now this was interesting. I remember some two weeks back created a self signed certificate with IIS in my laptop and then installing it. The same certificate was used to enable SSL over IIS and SQL Server.Please note I have IIS and SQL Server in my laptop.I also remember deleting this certificate last week.So concluded that there is some issue with certificate generated by SQL Server.

From registry editor went to the path

HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\MS$SQLINSTANCE_NAME\MSMSQLSERVER\SUPERSOCKETNETLIB

Deleted the value in Certificate key and then restarted the SQL Server service. Whoop it fixed the issue.

Read SQL Server Trace file using C# in VS 2010

DBAs love using the function fn_trace_gettable  to read  contents from a  trace file . Just pass the path of the trace file to this function and it will show the content of trace file.But what if developers want to read the same using .NET framework.You  can’t have the luxury of having SQL Server installed in every machine so that you can execute fn_trace_gettable. This function can’t be executed remotely using a linked server.

First thing what you need to do is open VS 2010 and give reference to SQL Server SMO  assembly Microsoft.SqlServer.ConnectionInfoExtended.dll and Microsoft.SqlServer.ConnectionInfo.dll

Once you have reference to these dll’s in your project then you can use  an object of  TraceFile class to read the content of TraceFile.

//Reads the trace file 

TraceFile trcFile = new TraceFile();
 
trcFile.InitializeAsReader(strFileName);

while (trcFile.Read())
{                   
     string strTextData = trcFile.GetString(trcFile.GetOrdinal("TextData")).ToString();

     string  strApplication=trcFile.GetString(trcFile.GetOrdinal("ApplicationName")).ToString();

}   

trcFile.Close();

But before reading these values you may need to check if these columns are null by using the code

trcFile.IsDBNull(trcFile.GetOrdinal("TextData"));

Once you have these values you can load it into a SQL Server remote database and then create reports based on these values.Please don’t forget to include Microsoft.SqlServer.Management.Trace namespace in your code.