SQL Server Unattended Installation

This post is to Automate SQL Server installations using unattended installation concept through command prompt.

To automate installation requires SQL Server ConfigurationFile, SQL Setup generates a configuration file based upon the dba inputs and files named ConfigurationFile.ini. The ConfigurationFile.ini file is a text file which contains the set of parameters in name/value pairs along with descriptive comments. Many of the parameter names correspond to the screens and options which you see while installing SQL Server through the wizard.
You can then use the configuration file to install SQL Server with the same configuration instead of going through each of the installation screens.

To create ConfigurationFile, follow the below steps:
1. Run SQL Server 2012 Setup as you normally would.
2. Set all of the configuration settings you want, clicking through all of the pages of the setup wizard.
3. When you reach the “Ready to Install” page, Setup will allow you to review all of your configuration settings. In addition, at the bottom of the screen it will show you the path of the configuration file that it has created itself. Make note of the configuration file path so you can grab the configuration file.

4. Cancel Setup.

Now, edit the ConfigurationFile as follows:
1. Set QUIET to “True”. This specifies that Setup will run in a quiet mode without any user interface

QUIET="True"

2. Set SQLSYSADMINACCOUNTS to “BUILTINADMINISTRATORS”. This will ensure that administrators on the machine are added as members of the sysadmin role. You can set its value based on your needs (Ex: SQLSYSADMINACCOUNTS=”domainYourUser”), but this is the more generic approach. I have added My user instead of BUILTINADMINISTRATORS, to secure SQL server from unwanted logins.

SQLSYSADMINACCOUNTS="BUILTINADMINISTRATORS"

3. Add PID and set its value to your product license key. If your setup.exe already comes preloaded with the key, there is no need to add this option to the configuration file.

4. Add IACCEPTSQLSERVERLICENSETERMS and set its value to “True”. This is to require to acknowledge acceptance of the license terms at time of unattended installations.

IACCEPTSQLSERVERLICENSETERMS="True"

5. Remove the UIMODE parameter as it can’t be used with the QUITE parameter.

6. Add the Directory of INSTALLSHAREDDIR, INSTALLSHAREDWOWDIR, INSTANCEDIR parameters. but if you want to install on the default installation directories then you can remove these parameters.

7. You can add or remove the feature you want to install, Select FEATURES=SQLENGINE,SSMS,ADV_SSMS in the configuration file. You can change that based on your needs.

For more details on features refer Article

Now ConfigurationFile.ini is ready, to execute that will create "SQLInstaller.bat" file which ask for the password and calls the ".ini".
Copy the below script and save as "SQLInstaller.bat"

@echo off
echo Installing SQL Server 2012

echo.
set /p SSvcpwd= Enter SQL Service Account Password "ENTERPRISE\SQLServices":

echo.
pause

date/t
time /t

"D:\SQLEntEdtn_2012_English\setup.exe" /SQLSVCPASSWORD="%SSvcpwd%"/AGTSVCPASSWORD="%SSvcpwd%" 
/ISSVCPASSWORD="%SSvcpwd%" /RSSVCPASSWORD="%SSvcpwd%" 
/ConfigurationFile="C:\SQLInstaller\ConfigurationFile.ini"

date/t
time /t

pause

That's it!!
Run the ".bat" as administrator, Enter the password and wait for sometimes. Installation will get complete after that verify the SQL Services & Instance.

SQL Server : Use of DBCC PAGE

Use of DBCC PAGE
One more undocumented command is DBCC PAGE. Use it to read the content of database MDF and LDF files.

NAME: DBCC PAGE

FUNCTION:
   Prints out the contents of a SQL Server page.

SYNTAX:
   DBCC PAGE ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

PARAMETERS:
   Dbid or dbname - Enter either the dbid or the name of the database
                    in question.
   Filenum- Enter the file number, 1- mdf & 2- ldf

   Pagenum - Enter the page number of the SQL Server page that is to
             be examined.

   Print option - (Optional) Print option can be either 0, 1, or 2.

                  0 - (Default) This option causes DBCC PAGE to print
                      out only the page header information.
                  1 - This option causes DBCC PAGE to print out the
                      page header information, each row of information
                      from the page, and the page's offset table. Each
                      of the rows printed out will be separated from
                      each other.
                  2 - This option is the same as option 1, except it
                      prints the page rows as a single block of
                      information rather than separating the
                      individual rows. The offset and header will also
                      be displayed.
                  3-  This option gives in brief info about the page.

Before running this command, you should first set the trace flag by executing command DBCC TRACEON(3604) and then DBCC PAGE('dbname', pagenum, pageid, )
To find the table page which you would like to read use, DBCC IND and in SQL 2012 onwards - sys.dm_db_database_page_allocations.
Example as below:

DBCC IND('mydb1',EMPLOYEE,1)
GO

Or, we can use the DBCC SEMETADATA('Objectname') command, syntax as below:

Use myDb1
Go
DBCC SEMETADATA(N'Employee')
GO

So now lets assume, you would like to view the page 165, syntax as below:

DBCC PAGE('mydb1', 1,165,3 )
Go

For more details refer this Article

SQL Server : Use of FN_DUMP_DBLOG

Use of FN_DUMP_DBLOG

fn_dump_dblog is an another undocumented hero in SQL Server function that read transaction log native or natively compressed backups.
Here is the syntax to execute fn_dump_dblog function on a specific transaction log backup and specify all 63 parameters:

SELECT 
[Current LSN], [Operation], [Context], 
[Transaction ID], [transaction name], [Description]
FROM fn_dump_dblog (NULL,NULL,N'DISK',1,N'D:\Mydb1\mydb1_trn1.trn',
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, DEFAULT); 

Here is the output:



using fn_dump_dblog function,
you can read the transaction's and identify the current LSN,
convert the Current LSN into the used format in Restore command similarly as in the last post i.e. use-of-fndblog

SQL Server: Use of FN_DBLOG

Use of FN_DBLOG

fn_dblog (Hero of this Post) is an undocumented SQL Server function that reads the active portion of an online transaction log.

Execute the below fn_dblog function to get the details:

Select * FROM sys.fn_dblog(NULL,NULL)

You can filter the result by adding where clause with options like - LOP_SHRINK_NOOP, LOP_INSERT_ROWS, LOP_MODIFY_ROW, LOP_DELETE_ROWS.
LOP_SHRINK_NOOP - This means taken log backup and shrinking the file.
LOP_INSERT_ROWS - Inserted new rows
LOP_MODIFY_ROW - Modified rows
LOP_DELETE_ROWS - Deleted rows

Lets now look into the situation where a user has deleted data and the techniques you can use to identify the user who has deleted data using the fn_dblog function and how you can restore up to the point prior to the data being deleted.

Let see through the example; Lets assume someone has deleted few rows from the table.
To be able to find any record of deleted data within the transaction log can be achieve using undocumented function fn_dblog and pass in two NULL values as shown below:

SELECT [Transaction ID], [Operation],[Context], [AllocUnitName]
FROM sys.fn_dblog(NULL,NULL) WHERE OPERATION = 'LOP_DELETE_ROWS';

Using the Null values means that I am not looking for a specific LSN Start and End point. When using this function a scan of the active log portion is carried out starting at the oldest uncommitted transaction up to the most recent record.
Result as below:



Using "Transaction ID" from above information we can identify who deleted the records using below script:
In this case we are taking [Transaction ID] = '0000:00001d88'

Use mydb1
Go

SELECT
[Current LSN],[Operation], 
[Transaction ID], [Begin Time],
[Transaction Name],
SUSER_SNAME ([Transaction SID]) [USER]
FROM sys.fn_dblog(NULL, NULL)
WHERE 
[Transaction ID] = '0000:00001d88'
AND
[Operation] = 'LOP_BEGIN_XACT'

The LOP_BEGIN_XACT is a log operation and includes information such as the SPID, transaction name and start time and by using the above script, you can find out the operation started at "2015/08/20 10:53:06:220" and was carried out by user "DOM\Amit_Bhardwaj".

With the above information now it is possible to restore the database prior to the deletion of rows. Using the "current LSN" number of "Transaction ID" -'0000:00001d88', will convert this value into a decimal value that shall be used within the log restore .
So the "Current LSN" full value is 00000045:00000109:0001 and break it and convert it as follows:-

Convert 1st Value- 00000045 from Hexadecimal to Decimal = 69
Convert 2nd Value- 00000109 from Hexadecimal to Decimal= 265
Convert 3rd Value- 0001 from Hexadecimal to Decimal = 1

Now using above value will create LSN number, which will use it into Restore Script with with the STOPBEFOREMARK option:
To Create LSN value,

Pick 1st Decimal Value- 69
Pick 2nd Decimal Value- 265
Pick 2nd Decimal Value- 1

Now as per LSN Number Syntax (LSN no.- 00 0000000000 00000)- 1st value contains 2 numeric decimal value (so its 69), 2nd value contains 10 numeric decimal value (so its 0000000265) and 3rd contains 5 numeric decimal value (so its 00001).
so according to this LSN number should be 69000000026500001

So first take the tail log backup.
Now Start the restoration:

1. Restore Full with norecovery
2. Restore log backup with norecovery and according to start time of the "Transaction ID" -'0000:00001d88' which is "2015/08/20 10:53:06:220". using this time restore this time log backup with the STOPBEFOREMARK option.

FUll backup mydb1_full.bak- taken at 2015/08/20 10:00
Tlog backup mydb1_trn1.trn taken at 2015/08/20 10:30
Tlog backup mydb1_trn2.trn taken at 2015/08/20 11:00

Here are the scripts to restore:

RESTORE DATABASE [mydb1] FROM  DISK = N'D:\Mydb1\mydb1_full.bak' WITH NORECOVERY
GO
RESTORE LOG [mydb1] FROM  DISK = N'D:\Mydb1\mydb1_trn1.trn' WITH NORECOVERY
GO
RESTORE LOG [mydb1] FROM  DISK = N'D:\Mydb1\mydb1_trn2.trn'
WITH RECOVERY STOPBEFOREMARK='LSN:69000000026500001'

SQL Server Orphaned User Issue

Orphaned Users are created, When a database is moved (Restore or detach/attach) from one server to another server the login SID's stored in the master database don't match/mapped with the login SID's stored in the user database.
Follow below steps to remove orphan users:

To get the report of Orphaned Users
To detect orphaned users, execute the following T-SQL statement:

USE DB_name
GO 
Exec sp_change_users_login 'Report'
GO

Note: sp_change_users_login cannot be used with SQL Server logins that are created from Windows.

To view difference in SID of Orphaned User

USE master
GO 
SELECT name as SQLServerLogin,SID as SQLServerSID FROM sys.syslogins
WHERE [name] = 'User_name'
GO

USE DB_Name
GO 
SELECT name DatabaseID,SID as DatabaseSID FROM sysusers
WHERE [name] = 'User_Name'
GO

The result of above query will shows that the SID of the SQL Server login and that of the user database SID differ and that's the cause of the problem.

To Resolve an Orphaned User
To resolve an orphaned user, use the following procedure:
1. Command to Auto map:

EXEC sp_change_users_login 'Auto_Fix', 'User_name';
GO

2. Command to create and map an orphaned user to a login

EXEC sp_change_users_login 'Auto_Fix', 'User_Name', null,'password';
GO

Script to script out T-SQL commands to auto fix all SQL Logins
Execute below command in 'Result to text' Mode

IF OBJECT_ID('tempdb..#Orphaned') IS NOT NULL 
 BEGIN
  Drop TABLE #orphaned
 END
Create TABLE #Orphaned (UserName sysname, UserSID VARBINARY(85))
Exec sp_MSforeachdb 'USE [?] ; INSERT INTO #Orphaned Exec sp_change_users_login ''Report'' ;
Select ''USE ['' + DB_name() + '']; '' 
+ ''EXEC sp_change_users_login ''''Auto_Fix'''','' + '''' 
+ [username] + '''' from tempdb..#Orphaned; Truncate TABLE #orphaned'

SQL Server with Kerberos Authentication

Good Morning everyone, Today will talk about How SQL Server works with Kerberos authentication? How to troubleshoot issues related to Kerberos in SQL Server? Kerberos authentication is very robust topic and complex, but implementing in SQL Server successfully isn’t...:)

When SQL Server starts, the account that is running the SQL Server Services tries to register a SPN (Service Principal Name) in Active Directory. The account that is running SQL Server Services requires “Read/Write ServicePrincipalNames” permission in AD. If successfully registered, an SPN for the SQL Server service is now mapped to the service account for SQL Server. Whether it is failed or success, you can verify by following below steps:

- In SQL Server Management Studio, browse to the SQL Server Error Log.
- Find the section of the log for the period SQL Server was last restarted.
- You should see an error message indicating whether SPN registration was successful or not.

If Success



If Failed



To view, create or delete SPN’s, there’s the Setspn command line utility. To get any use out of it, you need to know a couple of things about what you’re checking, such as the service account for your service, domain details, etc.

Check by which service account SQL Server services are Running

To Check follow below steps:
- Launch SQL Server Configuration Manager on the server running SQL Server.
- Select SQL Server Services and look at the ‘Log On As’ column for the SQL Server service.

In my case, the ‘TESTACC’ user account in the ‘TESTDOM’ domain is running the SQL Server services. This is the account we have to check when looking to see if there’s a mapped SPN. It may be that your SQL Server services are running under a local machine account, in which case you need to use the machine’s hostname when doing the SPN lookup.

List the SPN’s mapped to a principal

To list the SPN’s mapped to the ‘TESTDOM\TESTACC’ account:
- Open a DOS window
- Run setspn -L domain\account or setspn -L hostname, e.g.


What can you infer from this output? As per the Setspn technet article, the SPN format is: ServiceClass/HostName:Port/ServiceName

So, these SPN’s are for the MSSQLSvc service, running on the MYSERVER and MYSERVER2 hosts (FQDN) on ports 4444/44445 respectively. As well as the port number, the SQL Server instance name is enumerated by the command listing.

In my case, the service account running SQL Server had enough permissions in AD to register the SPN itself. But, if you get the failure message in your SQL Server Error Log, an administrator will have to manually register the SPN’s.

To manually register an SPN, you again use the setspn command line utility:
- Open a DOS window
- Run setspn -S serviceclass/host:port/servicename, e.g



When you use the -S switch, it first checks whether an SPN already exists in AD for the service. If so, you get an error stating that a duplicate SPN exists. If there is a duplicate, check its syntax is correct, and if needed use setspn -D to delete it, then recreate it as above.

Finally the conclusion for SQL Server, Service Principle Names (SPNs) are unique identifiers for services and identify the account’s type of service. If an SPN is not configured for a service, a client account will be unable to authenticate to the servers using Kerberos. You need to be a domain administrator to add an SPN, which can be added using the SetSPN utility.

SQL Server & its related features, the following SPNs need to be registered. Here are the formats to SETSPN:

--SQL Server Service SETSPN -S mssqlsvc/servername:1433 Domain\SQL
For named instances, or if the default instance is running under a different port, then the specific port number should be used.

--Reporting Services Service SETSPN -S http/servername Domain\SSRS SETSPN -S http/servername.domain.com Domain\SSRS
The SPN should be set for the NETBIOS name of the server and the FQDN. If you access the reports using a host header or DNS alias, then that should also be registered.
SETSPN -S http/www.reports.com Domain\SSRS

--Analysis Services Service SETSPN -S msolapsvc.3/servername Domain\SSAS
After Setup the SPN's, Instance must be restarted for the changes to take effect.

One thing you should consider regarding the SQL Server Serivce Account permission to get the SPN register itself whenever SQL Services restart and that is Enable Delegation (With Trust this user to delegation specific Services only - Kerberos Only)



For More SQL related issues refer this Article

For more details regarding Kerberos with SQL Server Reporting Services refer this Document