Recently, one of my colleagues asked me an age-old question – “How to ensure that I am able to access my SQL Server instance using my Windows login even if I forget the ‘sa’ login?” They had a long un-used instance of SQL Server for which they had forgotten the “sa” password. They had attempted to use Windows authentication, but had failed to login.

This incident was fresh in my memory when I re-installed the SQL Server instance on my test environment at home. Hence, it was the perfect time to write this post.

Proactive step

The ideal risk management strategy is to mitigate all them before they arise. Keeping this in mind, a simple step from the IT team (or whoever is in-charge of installing the SQL Server instance) can help ensure that at least one user always has access to a SQL Server instance at any given point in time.

As we all know, SQL Server supports two login mechanisms:

• Windows Authentication (default)
• Mixed Mode – SQL Server Authentication & Windows Authentication

In the interest of security, SQL Server has been designed such that Windows Authentication can never be turned OFF.

In SQL Server 2005, all members of the BUILTINAdministrators group were automatically members of the sysadmin fixed server role, and were therefore SQL Server administrators. Starting SQL Server 2008 however, this was no longer the case. In the “Database Configuration” screen of the SQL Server installer, one now needs to explicitly specify windows logins (or groups) that need to be added as sysadmins on the new SQL Server instance being installed.

Database Configuration Screen

When running the SQL Server 2012 installer, this information is requested in the “Database Configuration” screen (step #13):

As shown in the screen-shot above, you can do one of the following:

1. Add Current User – adds the currently logged-in user (under whose context the installer is running)
2. Add – Choose the domain or local user to add
3. Remove – Choose to remove any users added accidentally

The screen-shot below shows that when installing my SQL Server 2012 instance, I choose Mixed mode authentication and added myself as an admin:

Typically what happens is that when IT teams install SQL Server instances for development and QA teams, they set Mixed Mode authentication to ON, but forget to add one windows or local user as a SQL sysadmin. If the team now forgets the “sa” password, they are left with a SQL Server instance to which the applications can connect using other roles and users but cannot administer it anymore.

Moral of the story: The SQL Server installation checklists being followed by your team must have a check to ensure that at least one login (either that of the configuration manager on the team, or a local login) be registered as an administrator on the SQL Server.

Word of caution: Do note that whoever has been added to the sysadmin fixed server role has unrestricted access to the Database Engine.

Reactive step

While managing a risk in a proactive way is the ideal way, one might always find exceptions. As in the case of my colleague, it is possible to be left with an instance that one cannot administer because of not having the correct login credentials.

In such cases, I refer to an excellent blog post by my friend, Chintak Chhapia (blog) on How to access a newly installed SQL Server 2008 R2 instance if you do not know the login and password. Thanks to his recommendations, the colleague of mine had access to their server in almost no time!

Until we meet next time,

Be courteous. Drive responsibly.

At the start of the year, I wrote about the SQL Server upgrade advisor. I was setting up my home test environment recently and this prompted me to write a couple of posts on SQL Server installation and cover some of the nice features which I believe are quite important.

As you begin the SQL Server 2012 installation, SQL Server goes through a series of checks to ensure that environment configurations that could result in a failed installation are identified in advance and the user is given a chance to rectify them. In this set of checks, I noticed that the media also checks for long path names to the files:

What this check does is to simply check if all files on the installer can be accessed by a path that is less than or equal to the 256-character normal limit on path names. Most product installers do not perform this check and the installation ultimately fails when one has an unusually long file path (such that the number of characters is greater than 256 characters).

In my humble opinion, this is a great example of a check that all product installers must adopt to avoid installation failures for a reason as simple as a long path.

Until we meet next time,

Be courteous. Drive responsibly.

Have you ever encountered a need to find and/or validate the SQL Server features and their respective patch levels for any given server running a single or multiple SQL Server instance(s)? If so, the Installed SQL Server Feature Discovery Report generation utility is just the tool you are looking for.

Today’s post show you how to use this small utility for your documentation/inventory purposes.

Launch the SQL Server 2012 installer by clicking on the SQL Server Installation Center [You can also run the installer from the setup disc]

Under “Tools”, you will find the “option to launch the “Installed SQL Server Features Discovery Report”

Clicking on the tool starts the processing and you will be presented with the progress dialog, standard to all progress operations of the SQL Server 2012 installer.

Once execution is complete, the report will be opened automatically in Internet Explorer

If you need to send this report over to your IT team or preserve it for documentation purposes, you can find a copy at:

Path for Discovery Report file: <<SQL Server installation path>>Setup BootstrapLog<<Date time>>SqlDiscoveryReport.htm

Example: C:Program FilesMicrosoft SQL Server110Setup BootstrapLog20130407_103300SqlDiscoveryReport.htm

I trust this quick post will be helpful to you in the future.

Until we meet next time,

Be courteous. Drive responsibly.

Support for Microsoft SQL Server 2000 ends this year and yet, one can find systems (production or otherwise) that continue to run on and support SQL Server 2000. Recently, we were tasked with porting a piece of functionality backwards, i.e. from versions that support SQL Server 2012/2008 to SQL 2000/2005. Unfortunately, this piece of functionality was designed to work with newer versions of Microsoft SQL Server and involved the use of table valued parameters which is not possible in SQL Server 2000.

After a couple of hours of brain-storming, we realized that we could share temporary tables with the underlying stored procedures as long as the connection remained the same. Because the connection that created a temporary table is still active, the temporary table will continue to be accessible if the stored procedure is executed on the same connection. Here’s a small demo to help explain the solution.

Demo

For this demonstration, we will create a simple mathematical temporary table which will be updated from within a stored procedure. Assuming that the temporary table is named #tTableValues and has columns iA, iB and iC ( = iA * iB), let us first create a stored procedure which is designed to update the value of iA:

USE tempdb ;
GO
IF OBJECT_ID('UpdateTableValues') IS NOT NULL
DROP PROCEDURE dbo.UpdateTableValues
GO

CREATE PROCEDURE dbo.UpdateTableValues
@replacementValue INT
AS
BEGIN
SET NOCOUNT ON ;

IF OBJECT_ID('#tTableValues') IS NOT NULL
BEGIN
UPDATE tv
SET tv.iA = @replacementValue
FROM #tTableValues AS tv
END
ELSE
BEGIN
PRINT 'Temporary table: #tTableValues does not exist or access denied.'
END
END
GO

As you can see from the T-SQL script above, the stored procedure assumes that the temporary table is already available to it and directly makes the update. All the calling application now has to do is:

1. Open a SQL Server connection, say C1
2. Create the temporary table
3. Insert data into the temporary table
4. Call the stored procedure for the desired computation
5. Fetch the value/result once the stored procedure execution is complete
6. Drop the temporary table
7. Close connection C1

The T-SQL script below demonstrates this workflow:

--1. Connection has been created
USE tempdb ;
GO
--2. Check for and create the required temporary table
IF OBJECT_ID('#tTableValues') IS NOT NULL
DROP TABLE #tTableValues
GO

CREATE TABLE #tTableValues
(
Id INT IDENTITY(1, 1)
PRIMARY KEY CLUSTERED,
iA INT,
iB INT,
iC AS iA * iB
) ;
GO

--3. Insert some test data
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 1 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 2 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 3 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 4 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 5 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 6 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 7 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 8 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 9 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 10 ) ;
GO

--Debug point 01: Select from the temporary table
SELECT 'Debug Point 01' AS StepName,
Id,
iA,
iB,
iC
FROM #tTableValues ;
GO

--4. Execute the stored procedure
--NOTE that the connection has to be the same!
EXEC dbo.UpdateTableValues @replacementValue = 2 ;
GO

--5. Fetch the result of the stored procedure execution
SELECT 'Step 05 - Results' AS StepName,
Id,
iA,
iB,
iC
FROM #tTableValues ;

--6. Cleanup - the connection can now be closed
IF OBJECT_ID('#tTableValues') IS NOT NULL
DROP TABLE #tTableValues
GO

The screen-shot shown below shows the output of the execution and it can be seen that the value for iA in the temporary table created by the calling application was indeed updated by the stored procedure and the results were available after the stored procedure execution completed.

Conclusion

As demonstrated, temporary tables may be used for data exchange between an application and stored procedures or even between any two programmability objects (procedures, functions, etc). This functionality continues to remain valid even in the days of SQL Server 2012. The question, however is whether it would be preferable to use newer features like table valued parameters or not. Do let me know your thoughts on the same.

As for those who need to continue support of Microsoft SQL Server 2000 for their products, I trust this post was helpful.

Further Reading

• Temporary Table Naming – Maximum Allowed Length and associated naming logic
• Microsoft SQL Server 2000 Support Life Cycle

Until we meet next time,

Be courteous. Drive responsibly.

Most enterprise products require that they be able to audit data modifications being made within the application. An important aspect of auditing is to be able to identify changes made outside of the application too, i.e. updates directly made on the data using a direct connection to the SQL Server instance. I recently authored a series of articles on SQLServerCentral.com around data change and tamper detection mechanisms available in Microsoft SQL Server. These are:

• An in-depth look at change detection in SQL Server – Part 01 [Link]
• An in-depth look at change detection in SQL Server – Part 02 [Link]
• HASHBYTES: Is CHECKSUM really required? [Link]

Soon after the publication of these articles, a coincidence at work prompted me to write this post.

One of our application modules used HASHBYTES for detecting data change. We were receiving random reports that the quality assurance engineers were receiving auditing errors. As we tracked the issue down through the SQL Server Profiler, we noticed that the underlying error was a “simple” string truncation error:

Msg 8152, Level 16, State 10, Line 5
String or binary data would be truncated.

Root Cause

The following line from the Books-On-Line help for HASHBYTES [Link] caught our attention:

Allowed input values are limited to 8000 bytes.

As soon as we read it, things became very clear to us. Taking a quick look at the schema showed us that the underlying field being checked for changes was an NVARCHAR(MAX) field and the users were attempting to enter data greater than 4000 characters (i.e. 8000 bytes) causing the issue. As long as the data remained less than 4000 characters, no error was encountered.

A demo

The script below demonstrates the expected behaviour of HASHBYTES given that the input string is less than or equal to the 8000 byte limit. For the sake of brevity, I am using the REPLICATE function to create the string.

USE tempdb;
GO
--Declare an NVARCHAR((MAX) variable
DECLARE @tHashInputs NVARCHAR(MAX);
--Create a 4000 character string
SELECT @tHashInputs = CAST(REPLICATE('a',4000) AS NVARCHAR(MAX));

--Attempt to generate the HASHBYTEs value for this string
SELECT HASHBYTES('MD5',ISNULL(@tHashInputs,''));
GO

Now, let me alter the script such that the string being evaluated using HASHBYTES is greater than the 8000 byte limit, i.e. is 4001 characters for the NVARCHAR data-type:

USE tempdb;
GO
--Declare an NVARCHAR((MAX) variable
DECLARE @tHashInputs NVARCHAR(MAX);
--Create a 4000 character string
SELECT @tHashInputs = CAST(REPLICATE('a',4001) AS NVARCHAR(MAX));

--Attempt to generate the HASHBYTEs value for this string
SELECT HASHBYTES('MD5',ISNULL(@tHashInputs,''));
GO 

The following error is encountered upon executing the batch:

Msg 8152, Level 16, State 10, Line 5

String or binary data would be truncated.

As can be seen from the example above, HASHBYTES will fail for data beyond the documented 8000 character limit. Hence, areas like free-form comments may not be a suitable candidate for usage of HASHBYTES.

Solution

The only known workarounds that we have currently are:

• Split the strings into multiple parts before generating the HASHBYTES value
• Opt for any other change detection algorithm

Have you ever encountered such a situation before? If yes, what solution did you apply to overcome it?

References:

• HASHBYTES – Books On Line page [Link]
• An in-depth look at change detection in SQL Server – Part 01 [Link]
• An in-depth look at change detection in SQL Server – Part 02 [Link]
• HASHBYTES: Is CHECKSUM really required? [Link]

Until we meet next time,

Be courteous. Drive responsibly.