Archive

Posts Tagged ‘SQL Server 2005’

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

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

How to remove System Center Essentials 2007 remnants from Active Directory

April 27, 2009 5 comments

So I had to reinstall System Center Essentials 2007 today. I prepared a clean machine. Since I haven’t tested Windows Server 2008 and System Center Essentials 2007 with SP1 together, I decided go with Windows Server 2003 R2 with SP2. I installed the operating system, joined with the domain and installed the IIS 6. Also installed all the needed updates, service packs and .net frameworks needed by System Center Essentials 2007 SP1. Then came the SQL Server 2005 with Reporting Services. Now I was finally ready for the SCE 2007 SP1 again. But when I started the setup I got this error:

An error Essentials is already installed in this domain.

I started to search around the Internet and found some information what i thought might help me. I used the following steps to clear my domain from SCE 2007:

1. On the Domain Controller run the ADSI Edit (Start -> Run -> Adsiedit.msc. If you don’t have it installed check this post.

2. Right click on the “Domain” tree and select New – Query.

3. In the New Query window, enter the following:

Name: SCESCP Query

Root of Search:

Query String: (&(objectCategory=serviceConnectionPoint)(cn=SCESCP))

4. Click on OK.

5. Expand the Domain node in ADSI Edit and select the “SCESCP Query” node.

6. A “CN=SCESCP” object should be listed in the right-hand pane, with a distinguished name of “CN=SCESCP,CN=,CN=,DC=,DC=com”. This tells us which computer account has the SCESCP.

7. Delete both “CN=SCESCP” and “CN=SDKServiceSCP“, then close ADSI Edit.

Now it was possible to install SCE 2007 with SP1 as there was no obstacles in Active Directory.

-Eric