Windows Authentication via alias remapping

Question

So,

We have these virtual machines that we run our development sql server engines on so we aren't overlapping on each other's DBs, which is quite beneficial at times.  However there are also times when we aid in the debugging of each other's work which means we often connect to other VMs around the network. 

So, in the process of determining out how to setup Visual Studio "Remote Debugging" it came to my attention that the two systems really do need to be on the same network domain.  However, initially all of our VM machines are installed from an Image file, and thus start off all having the same name:

MyVM

So, in order to make a "Unique" name on the domain, i renamed mine to "JaedenVM" and rebooted it. However, the situation is rather interesting that we have a windows "Service" that is always running on the "target" machine, which in this case is my "JaedenVM" machine, and this service looks through it's settings and returns the list of valid "Connection strings" for the databases that exist on that machine. 

So, the MyService running on my actual developer PC has connection strings to "Server\Db1", and "SErver\Db2", where as the SErvice running on the JaedenVM has only a connection string to "MyVM\TestDb". 

this allows for a simple setting in our App.config to point to a single value "MyVM:3267" (the port of the service), and thus in my dev computers "hosts" file I point MyVM to the IP of my VM and I get the connection strings from the service running on my VM.  if i point MyVM to my local systems ip address, I get the connection strings for the company network databases.

The difficulty comes in when on the Virtual Machine the Connection string is "JaedenVM", which the dev system has no clue what that means.  However, if on the VM i add a hosts file entry that points "MyVM" to the same public IP address, SQL Server does not allow Integrated security.

Thus if I go to SSMS and Say in the Server to connect to: 

   JaedenVM, Windows Authentication.  <--- Connects with no problem

   MyVM, Windows Authentication. <-- This fails even though MyVM is the exact same IP address as JaedenVM via the windows hosts file

Why is this?

Thanks

Jaeden "Sifo Dyas" al'Raec Ruiner

 

 

 

---

"Never Trust a computer. Your brain is smarter than any micro-chip."
PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.

Answer 1

What is the error mesage?

If the error is the standard 18456 error, look in the SQL Server errorlog for the exact state, and then look at
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx
for an interpretation. That might give a clue.

What happens if you try to connect directly to the IP address?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

18452 is the error.

By directly connecting to the IP address, the system fails in a similar manner because SQL Server does not perform the DNS lookup.  So additionally, when utilizing a Hosts file remap of a name to an IP SQL Server can still connect via SQL Accounts but when using Windows Authentication it does not appear to back trace the IP value to find the appropriate Domain Controller for Authenticating that User account.

EG:

Computer: JaedenVM
User: jaeden
IP: 192.168.27.88

Hosts file:
MyVm  192.168.27.88

Log into SQL Server with Windows Auth:
Server Name: MyVM
Result: SQL Server Looks for user MyVM\jaeden. <- this user does not exist cause there is no MyVM computer

Server Name: 192.168.27.88
Result: SQL Server Looks for user 192.168.27.88\jaeden <- this user does not exist either (for some odd reason)

Server Name: JaedenVM
Result: SQL Server logs in correctly because the Windows user is JaedenVM\jaeden.

However, the more amusing factor is that when acting as part of the "Domain" (not as a stand-alone VM, but part of the same domain) the user account is NOT "JaedenVM\jaeden" but "CompanyDC\jaeden", and yet that still fails unless the server name is the same as the computer name.

My question is why it performs this way instead of performing the IP lookup for the appropriate DC user credentials, especially when operating in Windows Auth Mode.

Thanks

Jaeden "Sifo Dyas" al'Raec Ruiner

 

 

---

"Never Trust a computer. Your brain is smarter than any micro-chip."
PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.

Answer 3

I have not answered this, because I hoped that someone else would be able to chime in.

First, do you have a domain or a workgroup? If you have a domain, you are always logged in DOMAIN\Jaeden. If you have a workgroup, you are logged in as JAEDENVM\Jaeden; at least that is my experience.

I tried you scenario at home, where I have a workgroup, and I was able to connect through the alias in the hosts file. I don't really have a domain where I can test it.

If you set up a share on the VM, can you map the share if you use MyVM?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Ah,

Well that's the problem.

We have the VMs on the domain initially, however the VM can be run in two modes: Bridged or Host.  Bridged mode, means that the VM acts like another computer on the network, however, when accessing this VM from the hosting system there are some really strange slow downs, so on average we run them under Host mode, which means that they are using a virtual gateway on the Host system, and can thus only be see by the Host system.  In this mode the slow down doesn't occur, but they are subsequently not connected to the Domain and thus cannot compare domain logon's. 

In bridged mode or in Host mode, the same problem occurs, though I will admit I have not tried this in WorkGroup mode, with the VM as not a member of the domain.  the reason being, is that in order to use VS's remote debugging the VMs have to be on the same domain for connectivity to work just right.  otherwise security credentials seem to get mucked up. 

We have also tried this test utilizing the "Domain Server", which also has some databases on it.  I've tried:

GI\MyDB <- corporate DC with SQL Server Installed
DEVSERVER2\AnotherDB <- my development workstation with SQL Server installed
SQLDEMO <- Alias in DEVSERVER2's hosts file.
GI\Jaeden <- User Name

Using Windows Authentication:
GI\MyDB <- Works
DEVSERVER2\AnotherDB <- works
SQLDEMO\MyDB <- when SQLDEMO points to GI fails
SQLDEMO\AnotherDB <- when SQLDEMO points to DEVSERVER2 fails

So, perhaps it is the "Domain" level security that is messing up things, regardless of using a VM or not.  *shrug*

Thanks

J"SD"a'RR

 

 

 

 

---

"Never Trust a computer. Your brain is smarter than any micro-chip."
PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.