Tag Archives: DHCP

How to Reset a Lost SA Password in Microsoft SQL Server

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It’s happened to all of us.  Someone installs a SQL server and then promptly forgets the sa password without documenting it.  Normally, it’s not that big of a deal – just log in with a different account, right?  Oh, wait… there aren’t any.

Recently, I ran into a scenario where a corrupt Active Directory had been rebuilt, however the SQL server logins got lost in the shuffle.  Consequently, there was no way to log into the SQL server except for the sa account, and that password was long-forgotten.  Fortunately, there’s a way to fix this problem without reinstalling SQL and reattaching the databases.

Microsoft SQL Server has the ability to launch in Single-User Mode.  In this mode, any account that is a member of the local Administrators group will be able to log in to the server with sysadmin privileges.  To launch in Single-User Mode, one must use a startup parameter for the SQL instance in question.

While in Single User Mode, only one user can be connected at a time (as the name would imply).  You’ll connect and interact with the SQL instance from a command prompt using SQLCMD commands.

1. Open an elevated Command Prompt.

Admin-Cmd-Prompt.png2. Stop the SQL Instance.  The default is MSSQLSERVER.

net stop MSSQLSERVER

Open in new window

net-stop-mssqlserver.png3. Start the SQL Instance using the ‘/m’ switch and specifying you’ll use SQLCMD to interact with the instance.  The input following the ‘/m’ switch is case-sensitive.  There’s no indication you’re connected in Single-User Mode, so don’t worry if you don’t see anything.

net start MSSQLSERVER /m"SQLCMD"

Open in new window

net-start-mssqlserver-with-m-sqlcmd.png

4. Connect to the instance with SQLCMD.  Just type ‘sqlcmd’ and hit <ENTER>.  You’ll find yourself at a numbered prompt.  This means you’re connected to the default instance.  If you want to specify a particular SQL instance, just use the appropriate SQLCMD switches.  The syntax will be:

sqlcmd -SServerName\InstanceName

Open in new window

sqlcmd-prompt-1.png

5. From here, you use Transact-SQL (T-SQL) commands to create a login.  I’m going to create a login called “RecoveryAcct” and give it the password “TempPass!”.  Since you’re issuing T-SQL commands directly, you’ll need to use the ‘GO’ command, too.  There’s no indication the command was successful; you just end up back at a ‘1>’ prompt.  If you don’t get an error, you can assume all is well.

CREATE LOGIN RecoveryAcct WITH PASSWORD=’TempPass!’
GO

Open in new window

sqlcmd-creat-login.png

6.  Now, use more T-SQL commands to add the user to the SysAdmin role.  Again, you’ll need to use the ‘GO’ command and if you don’t get an error, you can assume all is well.

SP_ADDSRVROLEMEMBER RecoveryAcct,’SYSADMIN’
GO

Open in new window

sqlcmd-add-role.png7. To exit SQLCMD, type ‘exit’ and hit <ENTER>.  Next, stop the SQL instance and then start it again without the ‘/m’ switch so it is no longer in Single-User Mode.

net stop MSSQLSERVER && net start MSSQLSERVER

Open in new window

net-stop-and-net-start-mssqlserver.png8. Launch SQL Management Studio using SQL Authentication and log on as the user you just created.

sqlmgmtstud-logon-with-recovery.png9.  Now, you can look at your security settings and make the appropriate changes you need.  You’ll see your recovery account listed.

sqlmgmtstud-user-tree.png
You can also use the SQL Server Management Studio if you’re not comfortable with SQLCMD.  When you are starting the instance in Single-User Mode, you can always specify “Microsoft SQL Server Management Studio” instead of “SQLCMD” after the ‘/m’ switch in step 3.

net start MSSQLSERVER /m"Microsoft SQL Server Management Studio"
GO

Open in new window

I hope this helps you out.

 

.

 

Advertisements

An Overview of DHCP

Configuring network clients can be a chore, especially if there are a large number of them or a lot of itinerant users.  DHCP dynamically manages this process, much to the relief of users and administrators alike!

Dynamic Host Configuration Protocol (DHCP) is standard protocol used by most networks to assign IP information to clients so administrators don’t have to worry about assigning static IP addresses to each device.

For example, let’s say you are the administrator of a corporate network and you have been tasked with creating a wireless network for guests so they can access the internet when they are at your company’s location.

Imagine if you had to assign every address manually.  Each guest user would have to physically hand you their phone, tablet, laptop, etc. and you would have to then log on to their device and configure the wireless network settings manually, typing in the IP address, subnet mask, default gateway, DNS server settings and whatever else they needed.

Since IP addresses must be unique on the network, you’d have to make sure you had a spreadsheet or notepad handy to record which IP addresses you had assigned so you didn’t accidentally assign the same IP address more than once.  Of course, this also means the guest users have to come back and let you remove the settings you configured so you could then note on your documentation that the IP address is available for you to assign to someone else.

Obviously, this would be a nightmare for everyone involved.

If you were to configure DHCP for that wireless network, all of this would be handled for you dynamically and your time can then be better spent handling another virus outbreak then explaining to users, yet again, that opening attachments in emails from emissaries of African royalty looking for help with financial transactions is still a bad idea.

So, how does all the magic happen?

In order for this to work, there are two DHCP components which must be able to communicate with each other:  the DHCP Server and the DHCP Client.  The DHCP client is software whose job is to simply ask a DHCP server for an IP address.  The DHCP server is quite a bit more complex.

DHCP Scopes

dhcp-server-scope.pngThe DHCP Server is what manages the entire process.  Its job is to listen for client requests and then give them the information they need to communicate with the network.

This information is stored in the DHCP Scope.  The information the scope contains will be used by the clients to configure their network settings.  Because of this, the scope must define, at minimum:

  • A range of IP addresses which can be assigned to clients
  • The subnet mask for the network
  • The default gateway address to be used by the clients

The default gateway is actually optional, however without it, clients won’t be able to connect to any other network, including those on the internet.

Other information typically defined in the scope include:

  • The DNS servers the clients will use
  • The default DNS domain name for the clients
  • Time servers the clients will use for time synchronization

There are a ton of other options, but the ones I’ve listed are the most common.

DHCP Leases

dhcp-server-leases.png
Remembering that IP addresses assigned on the network must be unique, the DHCP server has to keep track of what IP addresses are currently in use so it doesn’t hand out duplicates.  It also has to make sure devices don’t keep their IP addresses indefinitely, otherwise the server could eventually run out of addresses to assign.

Because of this, clients are not given and IP address to own.  Instead, they are given a lease on an IP address.  The Lease Duration is the amount of time the client can use that IP address.  Clients will attempt to renew their lease before it expires, and the server will always renew it (with a few exceptions, of course).

DHCP Reservations

dhcp-server-reservations.pngThere are times when it might be necessary to make sure certain devices always use the same IP address no matter what, but you still want that device to be configured via DHCP.  To do this, simply create a DHCP Reservation.  As the name suggests, a DHCP reservation reserves a specific IP address for a specific client (identified by its MAC address).  A great example is a network printer.  You can create the reservation in the DHCP scope and when you connect the printer to the network, it automatically gets configured with the correct information.

DHCP Exclusions

dhcp-server-exclusions.pngOne other option worth mentioning are DHCP Exclusions.  DHCP scopes can be configured to exclude IP addresses from being assigned to clients even though those addresses are part of the range of addresses the DHCP hands out.  This way, if you have devices that must have a static IP address, but can’t be configured via DHCP (or you just don’t want to configure reservations), you can have that device on the network without worrying about its IP address being given to something else.

DHCP takes most of the work and worry out of managing client connectivity.   Not only does it automatically configures clients with a host of administrator-defined options, it also documents the IP information for each client.

Put it all together, and DHCP is a powerful tool which should be every network administrator’s toolbox.

I hope you’ve found this article informative.  If so, please click on the blue and white “Good Article” button below.

.