Archive

Posts Tagged ‘SQL Server 2008 R2’

How to recover sysadmin access to SQL Server 2005/2008 R2 when sa password is lost

May 9, 2016 Leave a comment

 

  1. Open the SQL Server Configuration Manager tool
  2. Navigate to SQL Server Services and Stop the SQL Server Instance that needs a sa password reset
  3. Right click the on the instance/service and select properties.
  4. 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.”

  5. 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’;
GO

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

-Eric

Batch Script to Open SQL ports on Windows Server 2008 R2

March 15, 2016 Leave a comment

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.

-Eric

Enable Remote Connections on SQL Server 2008 R2

August 20, 2010 1 comment

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.

-Eric

Categories: Microsoft, SQL Tags: