I have 2 SQL instances in 2 virtual machines. I'm able to see the other instance in one machine but I cant connect to it.
Probably some permission problem…but I don't know where to look.
Can someone help me?
edit:
the first problem was in fact not having TCP/IP enabled, solved that and the error changed now I have error 18452. (I'm trying to connect with windows authentication, also tried with sql authentication in that case I get error 18456)
Both SQL instances are default
edit2:
After changing the autentication to windows and SQL, I can login in one of the instances using sql autentication (user SA), on the other…it gives me error 18470, but everything looks ok with that login (at least for me… maybe one of you can tell me were should I look)
edit3:
After enabling login sa everything works as expected in SQL authentication mode.
I change my question to, what if I wanted windows authentication mode? how could I do it?
Best Answer
If you have domain account, and both of those VMs are also part of domain then all you need to do is just to add that user by creating new login is SSMS. (unfortunately I don't have enough "reputation" to add image to show, just google "how to create new login in sql server" and you should be able to find steps)
But if you don't have domain login or these VMs are not part of domain then I don't think you can use windows authentication.