Researching and Bypassing DNS Issues

So we have almost finished transition three out of four. Move to a domain - check. Move to a new file/print server - check. Number three was the biggie.

Move the servers to an off-site data center - almost check.

We moved thirty seven servers to a data center last weekend, changed all of the external and internal IP addresses, and planned to be up and running on last Monday morning. I can hear you chuckling as you read, and yes it was probably too ambitious, but that was the directive so we did it.

DNS issues, both internal and external, are one of the big problems in a transition like this. The DNS server must be properly updated with all IP changes, the local office DNS server must be updated as well, and the external registrar must be updated with new external IP addresses, which can take a few days to propagate.

So if you are like me, a non-networking guy supporting the transition, what are some of the best ways to work on connectivity issues for users? Start with DNS. Here are some of the main testing steps I took to evaluate problems.

1) Ping the name you are trying to reach. If it resolves to the correct IP address but times out, there may be a real connection issue. If it resolves to the old IP address or something else unexpected...

2) Flush the DNS. From the command prompt, just run "ipconfig /flushdns" and it will remove any cache of DNS resolution so you can verify the IP is coming from the latest changes on the DNS server. Also this is a good time to check the DNS1 and DNS2 IP addresses in the NIC properties to verify the PC/Server is pointing at the correct DNS server. Then Ping again. If you get the same problem, move on to the next step.
3) Ping the IP address. If you can't Ping it, it doesn't always mean anything since some servers are configured to prevent pinging them. But if you know you could ping it from another machine, you should be able to ping it from the current machine.

4) Check DNS Suffixes. If you have multiple domains like we do, during this type transition you could have some resources showing up under the wrong zone due to config errors. So perhaps SERVER1.domain1.com now is setup under SERVER1.domain2.com. If you add DNS suffixes to the suffix list in the NIC properties, you can allow more opportunity for short names to resolve. By that I mean when you commonly go to SERVER1 rather than typing SERVER1.domain1.com, the suffix list will kick in to help.

5) For file shares, use the IP. If you can't map a drive or reach a resource through \\SERVER1 then just type \\IPADDRESS and see if you can reach the file share. When DNS issues are in play, often it is best to just work around it until the dust settles. Make sure you document the places where you do this type of thing so you can go back and change them to names when the issues are resolved.

6) If you must, change the hosts file. I save this for a last resort, but especially on servers, it is sometimes necessary in order to get processes working quickly. The hosts file (on Windows XP/2003, find it at Windows\system32\drivers\etc\hosts) allows you to bypass the DNS server. You type in an IP address followed by the name you want to use to resolve to that IP address. For example, adding "1.1.1.1 MyPC" to the hosts file means that if you ping or reference "MyPC" it will resolve to IP 1.1.1.1. It is like a personal DNS server for your PC/Server managed only by you.

WARNING: If you make changes to this file, you are overriding the real DNS server so do it with caution, and preferably only do it on a temporary basis. If you leave in your changes, you risk having the IP changing again in the future, the DNS server updating, and you never getting the changes.

Those are some of the highlights of repeating issues and resolutions I used this last week. Hope it helps someone else in their time of transition.

Domain Move Part 2

After the first attempt at moving a SQL 2005 Server from one domain to another, I learned several lessons to help me make the permanent transition to the new domain. Here are a few of the key things I checked and/or changed.

- Verify that the new DNS server has entries for the short names of all servers that SQL Server needs to access. I found that several servers would resolve to [servername].[full domain name] but not to simply [servername]. I had to request CNAME entries for all the resources that required (due to extensive existing code) the ability to access the server through the short name.
- Make sure all services are running under local accounts.
- Make sure the Reporting Services execution account is running under a local account.
- If any objects are owned by domain level accounts, change ownership to local accounts.
- If any SQL Agent Jobs are scheduled to run under domain accounts, change them to local accounts.
- Check SSIS packages for any domain account ownership and change to local account.

I still had a few issues, but for the most part I was able to change one SQL 2005 Server from one domain to another and add a SQL 2005 and a SQL 2000 server to the domain from a workgroup with little trouble after checking the previous list and making changes as needed.

If you have any more tips, please feel free to leave comments to flesh out the topic.

First Attempt At Changing Domains on a SQL 2005 Server

Well I have finished my first pass at moving one of our three SQL Servers to our new domain. I had to roll it back for the moment, but I learned a great deal in the process. This server runs on Windows 2003 Server 64-bit and SQL 2005 Enterprise Edition 64-bit. This server also runs Reporting Services and Integration Services packages to run scheduled reporting, so there are a number of things to test.

1) I changed the DNS Server IP addresses in the network settings to point at the DNS server which is part of the new Active Directory.

2) I changed the domain from the current domain to the new domain and completed the required reboot.

3) I setup security groups from the new domain on the SQL Server and assigned them the appropriate permissions. By assigning the domain groups as logins, it simplifies adding and removing employee permissions by making it a simple Active Directory group addition or removal for the user account.

4) I could connect to the server from my local PC (which I had already moved to the domain) from Management Studio. That gave me comfort that basic name resolution and integrated security were working properly.

5) From an IE browser, I tried to pull up the reporting services browser. No luck. In researching it, I found that the encryption key was not working after the domain change. I also found that a domain account from the old domain was setup for "Execute as" permissions. I attempted to change this to a new domain account but it failed. Tried restoring the backup of the encryption key, failed. I tried creating a new encryption key, failed.

Basically everything I tried to get Reporting Services up and running under the new domain failed. I was able to join the old domain and reset Reporting Services back to a working state so I can do some more research on the issues I encountered. I am considering moving the server to workgroup security and getting everything working right first, and then joining the new domain. Until I do some more research I am not sure.

Based on month end and some other priorities, I probably will not get another attempt at this until the middle of September. If anyone reading this has some advice or some good articles/blog entries on the subject, I welcome them.

Preparing To Join A Domain and Change Domains

Currently I am managing three production SQL Servers:
SQL 2000 on Windows 2003 with Workgroup security.
SQL 2005 on Windows 2003 with Workgroup security.
SQL 2005 on Windows 2003 with Domain/AD security.

We just spent the weekend moving all the PC's to a brand new Domain controller with a new Active Directory. The servers are next.

I have been surprised at how little is on the web about joining an existing SQL Server installation to a domain, presumably because most people start on one or never move to one. There is a little more on changing domains, but it still seems a little sketchy.

So sometime in the next week, all three servers will be members of the new domain. These servers use IIS, heavy Reporting Services, heavy Integration Services, and extensive cross server connectivity.

Hopefully I will have a good report, but either way I will let you know the good, bad, and ugly.