Reset Sa Password in SQL Server 2008, 2008 R2

Below steps helps to reset the "sa" password if you forgot or lost without install SQL Server 

1. Login into server using Administrator login

2. Open SQL Server Configuration Manager

3. Select SQL Service - Properties - Advanced -- Need to add (-m;) parameter to startup parameter of the

SQL Service, to start SQL Server in single user mode to reset the "sa" password

Note - Dont give any space after semicolon. Add -m; parameter starting itself it looks like below

-m;-dc:\program..........

4 Restart the SQL Service

5. Open Command Prompt

6. Run SQLCMD

If you have two instances running on server say SQL 2008 R2, SQL 2012, then issue command as below

7. SQLCMD  -bhamare3\Named

After invoking the SQLCMD it should display 1> prompt indicates login into server with admin login

Possible Errors:

You may receive the below error when you run SQLCMD from command prompt

Login failed for domain\user or user doesnot have sysadmin permissions

If you got the above error please stop the SQL Service and change the built-in account to Local System and start the service in single user mode and run the below steps

1> select @@servername

2>go

1> create login [domain\PC3] for windows;

2> go

1> sp_addsrvrolemember 'domain\PC3','sysadmin';

2> go

If NP (named pipes) is not enabled in your system you may encounter the below error

C:\Users\bhamare3>sqlcmd

HResult 0x2, Level 16, State 1

Named Pipes Provider: Could not open a connection to SQL Server [2].

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

Then Enable the Named Pipes protocol from SQL Server configuration manager then again invoke 7th step

Issue the below commands to enable / reset sa password

1> select @@servername --> Make Sure Instance name is correct

2> go

1> alter login sa with password='Dhiraj@123'

2> go

1> exit

1> alter login sa enable

2> go

Remove the (-m;) parameter from the SQL Server startup parameters, Restart the SQL Service and then Open SSMS with SQL Authentication. Then change the sa password as you like. 

Increase the Number of SQL Server Error Logs

The SQL Server Error Log is a great place to find information about what is happening on your database server. You can execute the below TSQL command which uses the xp_readerrorlog extended stored procedure to read the SQL Server Error Log to find the location of SQL Server Error Log file used by the instance of SQL Server.

XP_READERRRORLOG

The parameters you can use with XP_READERRRORLOG are mentioned below for your reference:

1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...

2. Log file type: 1 or NULL = error log, 2 = SQL Agent log

3. Search string 1: String one you want to search for

4. Search string 2: String two you want to search for to further refine the results

5. Search from start time 

6. Search to end time

7. Sort order for results: N'asc' = ascending, N'desc' = descending

Location of Error Log File:

USE master

GO

xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc' 

GO

Increase the Number of SQL Server Error Logs :

By default, the error log is located at “Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG” and ERRORLOG.n files. A new error log is created when an instance of SQL Server is restarted. Also database administrators can run the DBCC ERRORLOG command or sp_cycle_errorlog system stored procedure to cycle the error log without recycling the instance of SQL Server. The most recent error log backup will have a name ERRORLOG.1, the second most recent error log backup will have the name as ERRORLOG.2 and the current error log will have the name ERRORLOG.

It is a BEST PRACTICE to increase the SQL Server Error Log from the default value of 6, because the error logs may contain critical information about your database server. As mentioned, by default there will be 7 error log files that exist, 6 archives and the current one.  When a new error log is created the oldest archive gets removed and that data is then lost forever.  So if you are trying to troubleshoot a system problem and are doing several restarts of SQL Server you may end up replacing all of your archives and then loose this valuable information in the error logs.

Steps to be followed to Increase the Number of SQL Server Error Logs in SQL Server 2008 R2 :

1. Connect to SQL Server 2008 r2 Instance using SQL Server Management Studio

2. In the Object Explorer, Click on "Management" and expand "SQL Server Logs"

3. Right click SQL Server Logs and click on "Configure" option from the drop down list as shown in the below snippet

4. This will open up Configure SQL Server Error Logs window as shown in the below snippet. Here, for Maximum number of error logs option you can specify a value between 6 and 99. In this example, I have changed the value from the default value of 6 to 10.

5. Once you have specified the new value for Maximum number of error log files click OK to save the changes.

To assign a TCP/IP port number to the SQL Server Database Engine

If enabled, the default instance of the SQL Server Database Engine listens on TCP port 1433. Named instances of the Database Engine and SQL Server Compact 3.5 SP1 are configured for dynamic ports. This means they select an available port when the SQL Server service is started. When you are connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.

To assign a TCP/IP port number to the SQL Server Database Engine

1. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for <instance name>, and then double-click TCP/IP.
2. In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. Right-click each address, and then click Properties to identify the IP address that you want to configure.
3. If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.
4. In the IPn Properties area box, in the TCP Port box, type the port number you want this IP address to listen on, and then click OK.
5. In the console pane, click SQL Server Services.
6. In the details pane, right-click SQL Server (<instance name>) and then click Restart, to stop and restart SQL Server.

After you have configured SQL Server to listen on a specific port, there are three ways to connect to a specific port with a client application:

• Run the SQL Server Browser service on the server to connect to the Database Engine instance by name.
  
  
• Create an alias on the client, specifying the port number.
  
  
• Program the client to connect using a custom connection string.

How to change server collation

The Server Collation acts as the default collation for all the system databases on that instance of SQL Server and also for the newly created user databases.

The Collation for an instance is specified during the setup of SQL Server, whereas this can be changed at any point of time by rebuilding the master database and specifying the new collation.

This operation will overwrite the system databases and hence it is strongly recommended to have a complete system backup before proceeding with this activity.

Before you proceed,

• Make sure you have backup of all user database, jobs, logins, maintenance plans, etc.. 
• Drop / Detach all user databases
• Rebuild Master database by specifying new collation

For SQL Server 2005:

Check the current Collation of the server by running the below script

SELECT SERVERPROPERTY('collation') AS [Server Collation]

Navigate to the setup path using command prompt and run the below query by changing the parameters

start /wait setup.exe /qb INSTANCENAME=SQL2005 REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=yourSApassword SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

This will start the GUI for setup

Once the Installation of Prerequisites is completed, you will be presented with the below screen

Click "Yes"

Once this configuration is completed, it will automatically close the GUI.

Now you can verify the change of collation by executing the below commands

SELECT SERVERPROPERTY('collation') AS [Server Collation]




For SQL Server 2008, SQL Server 2008 R2, SQL 2012,

Check the current Collation of the server by running the below script

SELECT SERVERPROPERTY('collation') AS [Server Collation]




Navigate to the setup path using command prompt and run the below query by changing the parameters

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=adminaccount /[ SAPWD= StrongPassword ] /SQLCOLLATION=CollationName

Wait for the configuration to complete

Verify the change of collation by executing the below commands




SELECT SERVERPROPERTY('collation') AS [Server Collation]

Once the activity of changing the collation is completed,

• Recreate / Attach the users databases
• Make sure to verify / recreate the jobs, logins, maintenance plans, etc..