Lost MS SQL Server admin access? No Problem

Today I by mistake removed my own windows account from my local MS SQL Server installation. That meant I couldn’t use the DB anymore. I didn’t have any other account setup on this DB because it is a local setup. What it meant was that the SQL Server setup was useless, and only way to use it was to uninstall the whole DB Server and install it again.

However I did some search and found following solution to recover access-

Step 1 :

net stop mssqlserver

or you can stop it from services.msc. Now open a command prompt as an administrator and type:

Step 2 :

net start mssqlserver /f /T3608 

This will start MS SQL server in single user mode.

Step 3 :

sqlcmd 

Step 4 : create new windows user

1 create login [<<DOMAIN\USERNAME>>] from windows;
2 EXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAIN\USERNAME>>', @rolename = N'sysadmin';
3 GO;

OR, create sql server login

1 CREATE LOGIN [testAdmin] WITH PASSWORD=N'test@1234', DEFAULT_DATABASE=[master];
2 EXEC sys.sp_addsrvrolemember @loginame = N'testAdmin', @rolename = N'sysadmin';
3 GO

Step 5 : close sqlcmd by CTRL+c

Step 6 : Login to SQL Server via SQL Server Mgmt. Studio

The curious case of SharePoint integrated SSRS Subscription

Some time ago I found that whenever I create an SSRS subscription with multiple cascaded filters in SharePoint integrated mode, the the time it takes to create subscription after selecting all filter values is more than the total time taken if the SSRS report were executed.

In order to understand why this happens, lets understand what happens when you create a subscription.

When you click on “Add Subscription”, all present data sets are executed. You might ask why?, well the answer is because present parameter and filter values need to be populated. This seems normal but the curious case that I’m talking about is something else.

The problem is with cascaded filters i.e. filters values depending on other filters values

Default behavior means all the datasets are fired when “add subscription” is clicked. Because child filter must have values that are derived from the values of parent filter in cascade, the dataset queries are fired multiple times, depending on how many cascaded children you have.

In a scenario where the data sets have complex and long queries, the problem can become very evident in terms of the time it takes to create a subscription.

But why does it happen. I asked this question to Microsoft. The response was – because of POSTBACK. Because it is SharePoint integrated mode, all calls are diverted through SharePoint and POSTBACK re-fires them. Another question I asked Microsoft was – why was it designed this way? They don’t have an answer to this. However they were kind enough to look further and fix this problem.

so here is the problem statement as defined by Microsoft and its solution:

Problem Description:

You’ve a report in SharePoint integrated mode which contains parameters. When you do an add subscription, the page takes a lot of time to come up. Also, when you change any of the parameters, the post back takes really long time to reload the page.

Analysis:

1. We took the profiler traces and found that a single query belonging to a data set is executing 4 times. This has been causing the issue.

3. We engaged our product team to have a better understanding of the behavior and how we can mitigate the same.

4. The reason behind the multiple execution of the query is due to the post back within ASP.NET.

5. We reviewed our code and found a scope for improvement, at least that would remove one level of execution.

6. Our Product team has agreed to release the improved code as part of SQL Server 2012 SP 2 CU 5.

Root cause:

1. Due to the post back behavior of ASP.NET, the queries have been executed multiple times.

Resolution:

A fix will be released as part of SQL Server 2012 SP 2 CU 5. The tentative release date is on March 16, 2015.