- Open the SQL Server Configuration Manager tool
- Navigate to SQL Server Services and Stop the SQL Server Instance that needs a sa password reset
- Right click the on the instance/service and select properties.
- Go to the “Advanced” tab, and in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option
Note! – Please make sure there is no space between “;” and “-m”. In the SQL Server ERRORLOG, there should be an entry that says “SQL Server started in single-user mode.”
- Click the “OK” button and restart the SQL Server Instance
When the SQL Server Instance starts in single-user mode, you can use the Windows Administrator account to connect to SQL Server using the sqlcmd utility and Windows authentication.
Connect to SQL Server using sqlcmd: sqlcmd –S SERVER1\SQLExpress
The following example adds the account “John” in the “EXAMPLE” domain to the SQL Server “sysadmin” role:
EXEC sp_addsrvrolemember ‘EXAMPLE\John, ‘sysadmin’;
Note! Once the sysadmin access has been recovered, remove the “;-m” from the startup parameters using the Configuration Manager and restart the SQL Server Instance
Here’s the script that opens the ports on Windows Firewall on Windows Server 2008 and R2.
Please copy the script below into the Notepad and save it as bat or cmd file.
netsh advfirewall firewall add rule name="Open Port 80" dir=in action=allow protocol=TCP localport=80
@echo ========= SQL Server Ports ===================
@echo Enabling SQLServer default instance port 1433
netsh advfirewall firewall add rule name="SQL Server" dir=in action=allow protocol=TCP localport=1433
@echo Enabling Dedicated Admin Connection port 1434
netsh advfirewall firewall add rule name="SQL Admin Connection" dir=in action=allow protocol=TCP localport=1434
@echo Enabling Conventional SQL Server Service Broker port 4022
netsh advfirewall firewall add rule name="SQL Service Broker" dir=in action=allow protocol=TCP localport=4022
@echo Enabling Transact SQL/RPC port 135
netsh advfirewall firewall add rule name="SQL Debugger/RPC" dir=in action=allow protocol=TCP localport=135
@echo ========= Analysis Services Ports ==============
@echo Enabling SSAS Default Instance port 2383
netsh advfirewall firewall add rule name="Analysis Services" dir=in action=allow protocol=TCP localport=2383
@echo Enabling SQL Server Browser Service port 2382
netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=TCP localport=2382
@echo ========= Misc Applications ==============
@echo Enabling HTTP port 80
netsh advfirewall firewall add rule name="HTTP" dir=in action=allow protocol=TCP localport=80
@echo Enabling SSL port 443
netsh advfirewall firewall add rule name="SSL" dir=in action=allow protocol=TCP localport=443
@echo Enabling port for SQL Server Browser Service’s ‘Browse’ Button
netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=UDP localport=1434
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE
I found this from Microsoft Support.
There are few things that needs to be sorted in order to get this running properly.
- Enable TCP/IP and Named Pipes protocols. In order to do that open Sql Server Configuration Manager (Start > All Programs > Microsoft SQL Server 2008 R2 > Configuration Tools > SQL Server Configuration Manager). Now on the left go SQL Server Network Configuration and select Protocols for YourInstanceName. Enable Named Pipes and TCP/IP.
- Allow Remote Connections. Open Management Studio, right click on SQL server and select properties. Under Properties select connections and make sure that under Remote Server Connections Allow Remote Server Connections has been ticked. If its not then tick it (enable it) and click ok.
- Create Firewall rules. Go to Control Panel > Administrative Tools and open Windows Firewall with Advanced Security.
SQL Server rule. Select Inbound Rules, right-click and select “New Rule”. Select Program and click Next, enter the path to the SQL server program (C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlserver.exe), click Next three times, then enter the name of the firewall rule (e.g. SQL Server), then click finish.
SQL Server Browser rule. Select Inbound Rules, right-click and select “New Rule”. Select Program and click Next, enter the path to the SQL server program (C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe), click Next three times, then enter the name of the firewall rule (e.g. SQL Server Browser), then click finish.
Once all this is done, you should have Remote Connections enabled for SQL Server 2008 R2.