SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Creating a SQL Server Test Lab On Your Workstation – Part Two, Creating a Domain Account and Joining a SQL Server to the Domain


In the second part of our series on creating a SQL Server test lab on your workstation, I’m going to look at creating a domain user, joining a SQL Server to our domain and connecting to that server using our domain account.

Other Posts in the Series

Part One, Installing The Domain Controller
Part Three, Creating a Windows Failover Cluster and a SQL Server Availability Group

Creating a Domain Account

The first thing that we’re going to do is create an account that we’ll be able to use to log into our domain and eventually connect to our SQL Server with.  Do do this, you’ll need to log into your domain controller and go to Active Directory Users and Computers.  You can get to that via the tools menu in the Server Manager.

2017-12-31 16_16_03-DC1 [Running] - Oracle VM VirtualBox

When the Active Directory Users and Computers window opens up, right click on your domain and select new\user.

2017-12-31 16_32_15-DC1 [Running] - Oracle VM VirtualBox

Fill in your user’s name, give it a login name and then hit next…

2017-12-31 16_23_20-DC1 [Running] - Oracle VM VirtualBox

Set it’s password and set any other setting that might be appropriate.  If you’re creating an account for someone else you might want to leave ‘User must change password at next login’ selected.  As this is just going to be a personal test lab for me, I’m not worried about that and as I don’t really want to be bothered with changing my password, I’ve also selected ‘Password never expires’.  Choose whichever options best suit you.

2017-12-31 16_25_10-DC1 [Running] - Oracle VM VirtualBox

We just need to click finish and we’ve successfully created our first domain account.

2017-12-31 16_28_15-

The next thing that I want to do with that account is to join it to the Domain Admins group, you may or may not want to do that depending on who that account is for but if you, like me are just creating a little, personal test domain then it might be a good idea.

Under users, find your new account, right click on it and select properties.

2017-12-31 16_35_13-DC1 [Running] - Oracle VM VirtualBox

Choose the ‘Member of’ tab, this will show you all the groups that the account is a member of and then hit ‘Add’

2017-12-31 16_36_32-DC1 [Running] - Oracle VM VirtualBox

Type ‘Domain Admins’ into the ‘Enter object names’ box, hit ‘Check names’ and then OK

2017-12-31 16_37_47-DC1 [Running] - Oracle VM VirtualBox

Our new account is now a member of the domain admins group.  We can now get on with joining a SQL Server into our domain.


Joining a SQL Server into the Test Domain

The first thing that we’re going to need is a server so go ahead and create a new virtual server in the same way as we did in part 1.

Once we’ve got the server up and running, we’re going to need to configure the network settings.  Start by going into the Network and Sharing Center and clicking on ‘Change adaptor settings’.

2017-12-31 16_56_16-SQL1 [Running] - Oracle VM VirtualBox

We now need to right click on our network adaptor and choose properties.

2017-12-31 16_57_10-SQL1 [Running] - Oracle VM VirtualBox

Choose ‘Internet Protocol Version 4’ and click properties…

2017-12-31 16_58_31-SQL1 [Running] - Oracle VM VirtualBox

Now give your server an IP address, I’ve gone for (I generally always put my severs in the range 10.100+ and workstations in the range 20.100+ but these are just my personal conventions, you can do what you like :)).  Default gateway and preferred DNS server’s should all be pointing to the domain controller that you created in Part 1 (IP if you’re following my conventions).

2017-12-31 17_02_46-SQL1 [Running] - Oracle VM VirtualBox

Hit OK and close and the server should now find the network.

Once that’s done, you’re going to need to join it to the domain.

This can be done through Server Manager and by clicking on the server’s name…

2017-12-31 16_42_22-SQL1 [Running] - Oracle VM VirtualBox

Click ‘Change’

2017-12-31 16_44_01-SQL1 [Running] - Oracle VM VirtualBox

Give the server a sensible name, I’ve gone for SQL1, pop in the name of your domain and click OK.

2017-12-31 16_45_32-SQL1 [Running] - Oracle VM VirtualBox

You’ll now be prompted for the name and password of a domain account.  Pop in the details of the account that we created earlier and hit ok.

2017-12-31 17_05_41-SQL1 [Running] - Oracle VM VirtualBox

If all goes well, your server will now be joined to the domain…

2017-12-31 17_07_55-SQL1 [Running] - Oracle VM VirtualBox

You’ll now need to restart your server.  When it comes backup, you should be able to log in using the domain credentials that we created earlier.

But I Can’t Seem To Connect To My Domain Controller

If you have issues connecting to the domain controller or joining the domain, it may be down to network settings and NIC settings (see the comment from londondba).

I’ve seen this a couple of times and londondba also mentions it, you may need to change the network settings on both the domain controller and SQL Server.

Depending on your version on Virtual Box you may be able to do this with the servers running or you may have to shut them down first.

In Virtual Box Manager, right click on the server and select ‘Settings’

In the network tab, you’ve got a few options under ‘Attached To:’

2018-01-04 08_24_03-SQL1 - Settings

Full documentation on the network types can be found HERE

If you’re having issues joining the domain then you should try setting the network to either ‘Internal Network’ or ‘Host-only Network’.

There is one main difference between the two, ‘internal network’ will only allow connectivity between virtual machines.  ‘Host-only Adapter’ is similar but will also create a virtual network card on your workstation which will allow you connectivity to the virtual network from your workstation.  I generally choose ‘Host-only Adapter’ for the extra connectivity.

One thing to note is that these setting will mean that the VMs will loose internet access.  If you need to connect to the internet (you need to download SQL Server or SSMS for instance) then you can temporary switch to ‘Bridged Adapter’.  There are better ways of doing this however and we’ll look at setting up an internet gateway as well as a few other options that Virtual Box gives us in a later post.

Install SQL Server

Now that you’ve got a server on the domain you’re free to start installing SQL Server.  I’m not going to step you through how to do that as I’m going to assume that you know that part.  If not then there are plenty of very good walk throughs out there on the web.  One point though, you’ll probably want to make sure that you give your new domain account SA access during installation.

With that done, you should be able to spark up SSMS and connect in to your SQL Server using the domain account that you’ve created.

In part 3 we’ll have a look at firewall settings, add a second SQL Server and create an availability group.

Thanks for reading ??

SQL Undercover

David Fowler and Adrian Buckman, two database nerds who love nothing more than to spend their time, reading about, researching and sharing all things SQL Server. David is a DBA with over 15 years production experience of SQL Server, from version 6.5 through to 2016. He has worked in a number of different settings and is currently the technical lead at one of the largest software companies in the UK. After working in the motor trade for over 11 years Adrian decided to give it all up to persue a dream of working in I.T. Adrian has over 3 years of experience working with SQL server and loves all things SQL, Adrian currently works as a Database Administrator for one of the UK’s Largest Software Companies.


Leave a comment on the original post [sqlundercover.com, opens in a new window]

Loading comments...