Archive

Archive for the ‘SQL’ Category

How to recover sysadmin access to SQL Server 2012 when sa password is lost

January 12, 2017 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 “Startup Parameters” tab, and in the Specify a Startup Parameter text box write  “-mSQLCMD”, click Add and then click OK.
  5. Restart the SQL Server Instance  which will now be running in single-user mode

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.\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 “;-mSQLCMD” from the startup parameters using the Configuration Manager and restart the SQL Server Instance

-Eric

Advertisements
Categories: Commands, Microsoft, SQL Tags: ,

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:

How to add MS SQL Server Management Studio (if you forgot to install it in the first time)

April 28, 2009 Leave a comment

The other day I had to install a brand new SQL Server for our production servers. So I decided to use Windows Server 2008 this time and prepared the machine. I installed the OS, updates and needed components, basically everything you need to prep the server for SQL Server. When I was done with that, I started the MS SQL Server setup. As I’ve done this hundreds of times it was next next next sort of thing for me. So after I was done telling the setup how to install my SQL Server I pressed install. Everything went down smoothly and the only thing I was told to do in the end was to install SP2, which I did. Then I decided it was time to fire up the SQL Server Management Studio and blimey was I surprised when I discovered that it was not there. Oh well, time to fire up good old MS SQL Server setup again I thought. I clicked next next etc until I got the selection where I knew I could select Management Studio from under Client Components I selected the Management Studio and I was good to go. I clicked next and nothing happened, I just got an error telling me that there was nothing new to be installed?? So I started digging around again and I found several blogs/technet forum posts about the similar issue but those solutions where a bit complicated (detailed explanations how to extract and install Management Studio?). As I was browsing the forums some guy mentioned something about going to Control Panel -> Programs and Features (Add/Remove Programs in Win 2003/XP) and running uninstall/change option from there. I decided to give it a try and it worked. I managed to install MS SQL Server Management Studio. Here are the steps:

1. Go to Control Panel -> Programs and Features (Add/Remove Programs in Win 2003/XP).
2. Find SQL Server 2005 and press change. Then this window pops up.

From there under SQL Server 2005 Common components select Workstation Components and hit next.
3. Now MS SQL Server Setup starts and you will get a choice – Change Installed Components or remove SQL Server completely.

Click on Change Installed Components and you will be able to select Management Tools from under Client Components.

Then click next two times and the setup will install Management Tools for you.

What I’m wondering is, what’s up with all the hassle? Why can’t I just install it straight from the cd/setup file ?

-Eric