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

Ken_Thompson_(sitting)_and_Dennis_Ritchie_at_PDP-11_(2876612463).jpg

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 10.10.10.100 (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 10.10.10.1 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 🙂

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

Add yours

  1. Loving this series. I had to set the VirtualBox networking to ‘Internal Network’ for both VMs so that they could see each other. Without this I was unable to register SQL1 on DC1’s domain.

    Like

    1. Glad you’re enjoying it 🙂 That’s not the first time that I’ve heard of the issue with having to set the network in internal only, thanks for letting me know about that, I’ll update the post and make a mention of it.

      Like

  2. Great Series. Just a quick question about the SQL Server installation. Did you choose Standalone, rather than failover cluster installation??

    Like

  3. Regarding getting internet access, you can just add a second adaptor under machine settings and set it to NAT. I saw this elsewhere and it worked just fine for me.
    I also got around the ‘unable to join the domain issue’ by going the Host-Only Adaptor route. (That was after a lot of googling/reading…if only I’d read on ahead I would have seen it was solved here too! But then that was how I stumbled on the 2nd-adaptor-with-NAT solution, so the cloud had a silver lining).
    As others have said, this is indeed a great series, really helpful! Thanks!

    Like

    1. Thanks Bob, really pleased that you’ve enjoyed it. 🙂 I have tried a second adapter before but then had issues with connectivity within the internal network. I tend to use a bridged adapter rather than NAT so that could be the difference.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: