All posts by srakeshsharma

back door entry to sql Server ! is this a loop hole to SQL Server security

One of my friend asked me a question that can we restrict Local Admins to gain Control of SQL Server. His is more concerned about the back door entry to SQL where a member of the local administrators group on the server can gain access to SQL Server by restarting it in single-user mode and then add himself as a login and add the login to the sysadmin group.

Well I doubt this can be restricted and I believe the answer is “NO”.  Admins can do anything within your SQL Server, however if the threat is that the Admin itself can be involved in data theft then there are other ways to restrict like Encryption in SQL Server or Vormetric Data Security. 

We can’t deny the fact that there are several ways by which a local or domain admin can take control of SQL Server. All a local or domain admin has to do is login as the SQL Server service account and they will be able to connect to the instance.  If they don’t know the password to that account, all a domain admin has to do is change the password for the account, launch SQL Server Configuration Manager, change the password there, then login with the service account, and connect to the instance (they don’t even have to shut the instance down following the service account change). 

If the local admin doesn’t have domain admin authority, they can still accomplish the same thing by creating a local account on the machine and going from there.  Or, they can simply change the service account for the SQL Server to be their own Windows account.

The only way you could possibly accomplish this is to create a login trigger that disconnects anyone coming in who is a sysadmin and isn’t in a list that you control within the SQL Server instance.  However this can still be bypassed this by starting the instance in single user mode or by using a trace flag. 

My take is that rather then looking it as a loophole we should consider it as a feature; in case if somebody messes with your SQL Server and delete all logins from inside; the Admins can still logins and fix the issue and take the control back. Coming back to security aspects there are several features available to stop the data theft like encryption features inside SQL Servers and  Vormetric Data Security at file level.

https://www.thalesesecurity.com/solutions/use-case/data-security-and-encryption/database-security/mssql-encryption

I had been into a situation where database sever was hacked by a hacker well known hacker “Hmei7” and the hacker inserted the java scripts inside the columns of several tables and then disabled the logins, hence you must need a back door entry to gain control of your SQL Server.

It’s the responsibility of the organization to assess their security and restrict the admin access to handful of people who are only authorized, and as a best practice quarterly review the access management policy.

how to get length\size of each row in table

CREATE PROCEDURE USP_GET_LENGTH_EACH_ROW  @TABLE_NAME VARCHAR(100) = NULL
/*  
--##  
AUTHOR - RAKESH SHARMA  
Ver.1.0  
DESCRIPTION - PROCEDURE WILL LOOP THROUGH EACH OBJECT AND CALCULATE THE SIZE OF EACH ROW  
***CAUTION - DO NOT RUN THIS ON PRODUCTION AS THIS MIGHT TAKE HOURS DEPENDING ON THE NUMBER OF ROWS AND THIS IS NOT RECOMENDED TOO  
***THERE ARE SEVRAL OTHER WAYS TO IDENTIFY THE FREE SPACE IN THE TABLE AND DATA FILES**  
INSTRUCTIONS:- HOW TO EXECUTE
	EXEC USP_GET_SIZE_EACH_ROW_1 (NO PARAMETER)
	DEFAULT PARAMETER =NULL --> WHICH MEANS THIS WILL LOOP THORUGH ALL THE TABLES AND CHECK SIZE OF EACH ROW
    
	PASSING TABLENAME TO GET THE SIZE OF EACH ROW
	EXEC USP_GET_SIZE_EACH_ROW_1 'TEST_COLUMN_STORE_INDEX'
*/  
AS  
SET NOCOUNT ON  
BEGIN  
DECLARE @table nvarchar(128)  
DECLARE @idcol int  
DECLARE @sql nvarchar(max)  
SET @idcol = 1  
DECLARE @COLUMN VARCHAR(200)  
IF @TABLE_NAME IS NULL
		BEGIN
						DECLARE emp_cursor CURSOR FOR       
						SELECT NAME  from SYS.SYSOBJECTS   WHERE TYPE='U'  
						OPEN emp_cursor      
						FETCH NEXT FROM emp_cursor       
						INTO @table      
						WHILE @@FETCH_STATUS = 0      
						BEGIN      
						SET @COLUMN=(SELECT TOP 1 COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@table)  
						PRINT @TABLE  
						PRINT @COLUMN  
						set @sql = 'select ' + 'ROW_NUMBER() OVER (ORDER BY ' + @COLUMN +') AS ROWNUM' +' , (0'  
							select @sql = @sql + ' + isnull(datalength([' + name + ']), 1)'   
									from sys.columns where object_id = object_id(@table)  
							set @sql = @sql + ') as ROWSIZE from ' + @table + ''  
						 EXEC (@sql)  
							FETCH NEXT FROM emp_cursor       
						INTO @table      
						END       
						CLOSE emp_cursor;      
						DEALLOCATE emp_cursor;      
			END
			ELSE
			BEGIN
						PRINT @TABLE_NAME
						SET @COLUMN=(SELECT TOP 1 COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TABLE_NAME)  
						PRINT @TABLE_NAME  
						PRINT @COLUMN  
						
						set @sql = 'select ' + 'ROW_NUMBER() OVER (ORDER BY ' + @COLUMN +') AS ROWNUM' +' , (0'  
							select @sql = @sql + ' + isnull(datalength([' + name + ']), 1)'   
									from sys.columns where object_id = object_id(@TABLE_NAME)  
							set @sql = @sql + ') as ROWSIZE from ' + @TABLE_NAME + ''  
						 EXEC (@sql)  
			END
END  

  

how to get table and avg. rows size in sql server

ALTER PROCEDURE USP_GET_TABLE_SIZE_AVG_ROW_SIZE  
/*
DESCRIPTION - PROCEDURE WILL CALCULATE AVG SIZE OF ROW SIZE  
***CAUTION - DO NOT RUN THIS ON PRODUCTION AS THIS MIGHT TAKE HOURS DEPENDING ON THE NUMBER OF ROWS AND THIS IS NOT RECOMENDED TOO  
***THERE ARE SEVRAL OTHER WAYS TO IDENTIFY THE FREE SPACE IN THE TABLE AND DATA FILES**  
***NOTE:- TEST THIS SCRIPT ON TEST MACHINE FIRST MODIFY ACCORDINGLY BEFORE EXECUTING ON PRODUCTION
*/  
AS  
SET NOCOUNT ON  
BEGIN  

IF OBJECT_ID('tempdb..#SpaceUsed') IS NOT NULL  
 DROP TABLE #SpaceUsed  
  
CREATE TABLE #SpaceUsed (  
  TableName sysname  
 ,NumRows BIGINT  
 ,ReservedSpace VARCHAR(50)  
 ,DataSpace VARCHAR(50)  
 ,IndexSize VARCHAR(50)  
 ,UnusedSpace VARCHAR(50)  
 )   
  
DECLARE @str VARCHAR(500)  
SET @str =  'exec sp_spaceused ''?'''  
INSERT INTO #SpaceUsed   
EXEC sp_msforeachtable @command1=@str  
  
  
DECLARE @old_tname VARCHAR(100)  
DECLARE @avg_row INT  
DECLARE @tname VARCHAR(100)  
DECLARE @data INT  
DECLARE @row INT  
CREATE TABLE #size (tname VARCHAR(1024),  
row INT,  
reserve CHAR(18),  
data CHAR(18),  
index_size CHAR(18),  
unused CHAR(18))  
CREATE TABLE #results (tname VARCHAR(100),  
data_size INT,  
rows INT)  
SELECT TOP 1 @tname=name FROM sysobjects where xtype = 'u' ORDER BY NAME  
SET @old_tname = ''  
WHILE @old_tname < @tname  
BEGIN  
INSERT INTO #size exec ('sp_spaceused ' + @tname + ',true')  
SELECT @row=row, @data=substring(data,1,CHARindex(' ',data)-1) from #size  
INSERT INTO #results values (@tname,@data,@row)  
SET @old_tname = @tname  
SELECT top 1 @tname=name from sysobjects where xtype = 'u' and name > @old_tname order by name  
end  
  
SELECT TableName, NumRows,   
CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpace_MB,  
CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpace_MB,  
CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpace_MB,  
CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpace_MB  
FROM #SpaceUsed  
ORDER BY ReservedSpace_MB desc  
  
  
SELECT UPPER(left(tname, 100)) AS TABLE_NAME,  
rows AS TOTAL_ROWS, (DATA_SIZE/1024) DATA_SIZE_MB,  
CASE rows WHEN 0 THEN 0  
ELSE (1024*cast(data_size as float))/cast(rows as float)  
END as AVG_ROW_SIZE_KB,  
CASE rows WHEN 0 THEN 0  
ELSE 8024.0/((1024*cast(data_size as float))/cast(rows as float))  
END as NUMBER_ROWS_PER_PAGE  
FROM #results  
DROP TABLE #results  
DROP TABLE #size  
END  
  

Tips to Pass AZ-900

Recently I passed AZ-900 Exam of Microsoft Azure Fundamentals, though its a fundamental exam but it covers most of the component in Azure. This exam is the beginning point for your Azure journey. Don’t take this exam lightly as the name suggest fundamentals. So here are some of my experience and tips to pass the exam.

Test will be comprised of 48 questions which you have to attempt in 90 minute and questions will cover most of the aspects from the fundamental point of view.

Questions will primarily come from these below topics.

  • Capex & Opex fundamentals
  • Examples of Private, public and Hybrid Cloud
  • Characteristics and benefits of each cloud model
  • Pricing, cost benefits and azure pricing calculator
  • Definitely there will be some questions on Availability zones, regions for example how many VM’s and Availability zones are required for 99.99% Uptime
  • When to use Pay-as-You-Go, Enterprise and Free ; which subscription to use for Azure App Services when you are planning to evaluate it for 6 months etc.
  •  Questions from Subscription, like a single account can manage multiple subscription, how many subscription can a single account own,  how many subscription are needed for different bills for different department. Can multiple subscription be merged
  • Questions on resource and resource group, for example can you have resources from multiple region in on resource group, how to protect a resource from being deleted so go thoroughly with Lock,
  • There will be question like deleting a resource group will delete all resources
  • Questions from RBAC, Azure Advisor , Microsoft Trust Centre
  • Questions on Azure Firewall, 
  •  Questions on Azure AD
  • Questions on when to use Azure IOT, Cognitive Services, Azure DevOps and Azure Application Insight
  • Some questions on Azure PowerShell, Azure CLI, for example can you manage resources  from IPhone and which tool you can be used on Iphone to manage Azure resources
  • What is DDOS in Azure, when this is used, difference between DDOS, Azure Firewall and Application gateway, what you will use to apply filter on protocol, what to use for filtering IP etc.

Unable to add these performance counters in Windows Server

There are chances that performance counters on the servers gets corrupted because of change\modification in the registry by extensible counters or because of WMI based program which has modified the registry.

\Memory\Available MBytes
\Memory\% Committed Bytes In Use
\Memory\Cache Faults/sec
\Memory\Cache Faults/sec
\PhysicalDisk(*)\%Idle Time
\PhysicalDisk(*)\Avg. Disk Queue Length
\Network Interface(*)\Bytes Total/sec”

 

To rebuild the performance counters run the below command

C:\WINDOWS\System32>lodctr /r

WindowsPC

To confirm the performance counters execute the below command

C:\WINDOWS\System32>lodctr /q

PerformanceCounterVerify

 

How to disable specific performance counters in windows?

Disabling some performance counters in windows is really easy.  You can control performance counters enable and disable from below windows registries

HKLM\SYSTEM\CURRENTCONTROLSET\SERVICES\%SERVICENAME%\PERFORMANCE

%ServiceName% represents any service with a performance counter like PerfDisk,PerfOs etc.

There may be registry keys for “Disable Performance Counters” in any of these locations. If the Key is not available create a Key with DWORD32 Bit and set it value to 1 if you want to disable that performance counter

PerformanceCounterED

Once the value is set to 1 for “Disable Performance Counter” the user will get this message as shown below.

PerformanceCounterED1

How to add article in Transactional Replication

Let’s consider a scenario we have a database in which we have 3 tables 2 of them are already added in replication and we want to add a third table in publication.

This is more important when we have a larger database and we don’t want to reinitialize the replication.

To avoid complete snapshot of the Publisher database and to avoid re-initialization we need to make some changes in the existing replication. We need to set 2 properties to False as shown below

  1. allow_anonymous
  2. Immediate_sync

Execute below commands

Now add table T3 again from Publisher properties and press ok.

  1. USE
  2. GO
  3. EXEC sp_changepublication @publication = ‘PubDB’,
  4. @property = ‘allow_anonymous’,
  5. @value = ‘false’
  6. GO
  7. USE
  8. GO
  9. EXEC sp_changepublication @publication = ‘PubDB’,
  10. @property = ‘immediate_sync’,
  11. @value = ‘false’
  12. GO

addtable3

Now add the new articles in existing publication

addtable3_pub

Now start Snapshot Agent

startsnapshot

Now you can notice that it only creates a snapshot of one article instead of all articles, now start log reader agent if it’s in the stopped state.

snapshot

Now as you can see a snapshot was generated but only for one article, so this article will be replicated to all subscriber without impacting existing replication.

Don’t forgot to execute below command at last to enable the disabled properties.

  1. Use
  2. GO
  3. EXEC sp_changepublication @publication = ‘PubDB’,
  4. @property = ‘allow_anonymous’ ,
  5. @value = ‘True’
  6. GO
  7. Use
  8. GO
  9. EXEC sp_changepublication @publication = ‘PubDB’,
  10. @property = ‘immediate_sync’ ,
  11. @value = ‘True’
  12. GO

replsyncoption

Now you can verify the article on all your subscribers

If you want to know more about immediate sync command follow below link

  1. Immediate_sync

 

 

One Way Merge Replication

Recently I received a requirement from application team to configure one way replication although it sound weird but yes this is possible and Microsoft provides some parameters to tweak normal behavior of Merge Replication.

Before we proceed further let’s discuss about normal behavior of Merge Replication and when to use Merge Replication.

Merge replication is one of the modes available in the Microsoft SQL Server for distributing data to various servers from a primary server. Merge replication is one of three types of replication, along with snapshot replication and transactional replication. Which type is used depends on the database’s needs, how frequently changes are made to it and the SQL Server version being employed.

Merge

https://technet.microsoft.com/en-us/library/ms151329(v=sql.105).aspx

Merge replication is the most complex type of replication because it allows both publisher and subscriber to independently make changes to the database. In this scenario, it is debatable whether the publisher is strictly the primary server, because other servers can also make changes to the data. At any rate, the changes are then synchronized by merge agents that sit on both servers, as well as by a predetermined conflict resolution mechanism in case of clashing data changes. Such clashes may arise because merge replication does not require a real-time network connection between the publisher and the subscriber, which raises the very real possibility of one server changing data, and another server later changing the very same data to a different value.
Here is an article which explains about conflict detection and resolution.

https://technet.microsoft.com/en-us/library/ms151749(v=sql.105).aspx

Merge replication is commonly used by laptop and other mobile users who cannot be constantly connected to the publisher, but still need to carry around a copy of the database that they can make changes to.

Are there any disadvantages for Merge Replication?

  • It takes lot of time to replicate and synchronize both ends.
  • There is low consistency as lot of parties has to be synchronized.
  • There can be conflicts while merge replication if the same rows are affected in more than once subscriber and publisher. There is conflict resolution in place but that adds complication.

Why there is a need for one way merge are there any advantages?

Consider a scenario where application needs lesser number of jobs less administration of jobs, distribution database and want to get rid of Publisher to Distributor latency and specifically for handheld\mobile devices\Read only data stores who cannot be constantly connected to the publisher, but still need to carry around a copy of the database.

It is also quite possible that you need this kind of setup as there are some constraint adding primary keys in your database\application and still you need some specific set of data proliferation to other sites. But you have t be careful on this as this will add additional RowGuid columns in table, and that you have to take care on front end.

From the advantages point of view I only see that with this One Way Merge you can get rid of Log Reader Agent, you will not see issues like log reader agent is stuck while scanning thousands of VLF (virtual Log files) when log grows too much in certain scenario and you don’t have to bother much about conflict resolution.

How to configure One Way Merge Replication?

There are 2 ways to achieve this kind of setup where we want data to be pushed from Publisher to Subscriber.

Method 1

The First way is at the article level where we can decide that a merge table article is download-only with an option whether you want to make changes at the subscriber but they will not be uploaded to the Publisher and another is changes are not allowed at the subscriber at all. This is achieved by using below property.

When adding an article, there is an option to define the subscriber_upload_options“:

sp_addmergearticle @subscriber_upload_options= subscriber_upload_options

This defines restrictions on updates made at a Subscriber (with a client subscription). The parameter “subscriber_upload_options” is a tinyint, and can have one of the following values.

0 No restrictions. Changes made at the Subscriber are uploaded to the Publisher.
1 Changes are allowed at the Subscriber, but they are not uploaded to the Publisher.
2 Changes are not allowed at the Subscriber.

@subscriber_upload_options =0

@subscriber_upload_options =1 

@subscriber_upload_options =2 

 

Merge1

Hence to achieve this functionality we have an option to choose 1 (Changes are allowed at the Subscriber, but they are not uploaded to the Publisher) or  2 (Changes are not allowed at the Subscriber).

 

Merge3Merge2

 

@subscriber_upload_options =1  is defined as “download only, but allow subscriber changes”. In this option there will be no triggers at the subscriber so there will be no firing of triggers to unnecessarily log metadata at the subscriber, which makes both subscriber data changes and the subsequent synchronization significantly faster.

@subscriber_upload_options =2 disallows all subscriber changes. In this case there is a special trigger – MSmerge_downloadonly*  which will rollback any attempt to change subscriber data.

Msg 20063, Level 16, State 1, Line 1
Table into which you are trying to insert, update, or delete data has been marked as read-only. Only the merge process can perform these operations.

Merge4

Once you are done with the configuration of Publication add subscriber where you want to download these articles and subsequent transactions with subscription type property as “Client”.

Merge5

This is how you can achieve one way merge replication by changing the property of published articles sp_addmergearticle @subscriber_upload_options= subscriber_upload_options

Method 2

There is another way by which you change the normal behavior of merge replication and force the merge replication into Unidirectional Merge. This can be achieved by changing normal behaviour of Merge Agent using property “EXCHANGETYPE” 

The value of -EXCHANGETYPE determines the direction of merge replication changes. This can be done by manually editing the Merge agent job step by adding -EXCHANGETYPE parameter with value 2

  • UploadOnly (1): Only changes originating at the Subscriber are merged with the Publisher. The Publisher’s changes stay in the Publisher. Use a value of 1 in your agent properties.
  • DownloadOnly (2): Only changes originating at the Publisher are merged with the Subscriber. Use a value of 2 in your agent properties.
  • Bi-Directional (3 – Default): Changes originating at the Publisher and Subscriber are merged. Use a value of 3 in your agent properties.
UPLOAD 1 Only merge Subscriber changes with the Publisher.
DOWNLOAD 2 Only merge Publisher changes with the Subscriber.
BIDIRECTIONAL 3 Merge all changes between the Publisher and Subscriber (default).

As soon as we configure with parameter with value of 2 it means that changes to a replicated article at the subscriber are not prohibited, are recorded in the merge metadata tables via merge triggers, and are subsequently filtered out when the merge agent synchronizes. This means there may be a huge amount of metadata unnecessarily recorded, slowing down data changes and synchronization.

 

Merge6

Conclusion: One Way or Unidirectional replication can be achieved easily in Merge Replication with minor tweaking in SQL 2005 and later version even at very granular level.

sp_addmergearticle @subscriber_upload_options =1 parameter defines restrictions on updates made at a subscriber. The parameter value of 1 is described as download only, but allow subscriber changes and seems equivalent to the -EXCHANGETYPE = 2 setting mentioned previously, but in the SQL Server 2005 case there are no triggers at all on the subscriber table. Another distinction is that this setting is made at the more granular article level rather than set for the entire publication. This means that although the -EXCHANGETYPE and sp_addmergearticle methods are logically equivalent, the implementation has become much more sophisticated in SQL Server 2005 and later versions. Triggers that unnecessarily log metadata at the subscriber are no longer fired; therefore both subscriber data changes and the subsequent synchronization are significantly faster.

@subscriber_upload_options =2 disallows all subscriber changes. In this case there is a special trigger MSmerge_downloadonly* which will rollback any attempt to change subscriber data. 

However you have to be very sure while using this option as this option is depreciated feature list and about Re-initialization of subscription with this kind of setup.

Deprecated Features in SQL Server Replication

I would highly appreciate feedback and comments on this article and would love to know more about any advantages you are getting from this kind of setup if you are already using this Unidirectional Merge.

 

 

 

SQL Performance Analyzer and Health Check

Sara Performance Analyzer

Sara Performance Analyzer (SQL Administration and Reporting Analysis) is a windows based application which helps you in troubleshooting SQL Server issues. The application perform health check on various parameters and provide a full report for your SQL Server. The tool provide detailed information on below parameters

  • Hardware
  • Disk Status
  • Services
  • Instance
  • DB Configuration
  • Backup Status
  • Temp DB Status
  • Performance
  • Job related information

SARA1SARA2SARA3SARA4

Download the zip file and extract all the file in folder and run setup.exe. This will install application with name SARA your machine.

>>Download Sara Performance Analyzer<<

How to run this tool and pre-requisites for this application

  • You must be SYSADMIN on SQL Server and Server Admin on Machine to run this application
  • Application enables XP_Cmdshell to execute some command and then disable it accordingly so test this in your DEV and Test environment before executing it on production
  • Use SQL Authentication OR Integrated option
  • Enter Server Name
  • Select Authentication
  • Click Process

This application will take around 1-3 minutes to finish all test and then displays result in Data Grid.

SQLCodeBlock

SQLCodeBlock is a small tool that contains all day today scripts that database administrators uses for troubleshooting and normal SQL Server health checks. Its a small initiative to collaborate all scripts and assembled them into a windows based application.

You can download and install this tool on you local machine and run it any time, its very easy to use, just open this tool select category,select sub-category and then click fetch script and then click copy to clipboard. CodeBlock2CodeBlock3CodeBlock4

>>SQLCodeBlack Download<< One Drive

>>SQLCodeBlack Download<< Google Drive

Download the zip file and extract the files and click setup.exe to install it on your machine