November 15, 2016 at 6:41 am
Hi,
We have an external server that is in DMZ and not in the domain connecting to our SQL cluster.
On this server there is a service that needs to have connection to SQL
Our SQL server is Windows Authentication only.
To make this work I created a local windows user on the external server and created the same windows user on both SQL servers with the same password. This to enforce NTLM pass-trough authentication.
This works, the only issue I had is that I can't map the windows Login to the database user on the passive node as the database is read only.
I also can't do it from the active node as there it says the Windows User is not known (it is only known on the passive node).
I would like to avoid having to do a failover just to do this. When I search on the internet everybody seems to be saying it can only be done by doing a failover.
However, I'm 100% sure I have already done this on our acceptation environment without needing to do a failover a couple of months ago.
I did it by some script I found on the internet after a 5 minute search.
Now I have searched for over 2 hours and I however can't figure out how on earth I did.
If I look on our Acceptation environment where It works correctly I see that the Server login on both servers have a different name but use the same SID. In the database this SID corresponds with one user.
I don't know how I managed to create a login using windows authentication on both servers using the same sid.
I only see an option to set the SID for SQL logins.
Could somebody help me, i'm a little frustrated by this. Thx!
November 15, 2016 at 8:12 am
The SID on a SQL login can be specified but the SID of a Windows account is defined on Windows level (like in Active Directory). If you want the SID of a local Windows account to be the same on seperate servers, I guess you need to look for a way to specify the SID when creating an account in Windows. When this SID is set (or perhaps changed?) it will be used when creating an associate login in SQL.
I don't know how to do that, but perhaps if you search the internet specifying the creation of an account in Windows (instead of a SQL login) you'll get results to help you further.
November 15, 2016 at 9:47 am
I found out that on our acceptation environment the user in windows had the same sid on both servers.
Apparently the servers were installed using the same sysprep image or something and because of this the sid of the computers was the same.
The user sid seems to be created by taking the computer sid + an incremental number, and by coincidence this number was the same as well.
Now I understand why i got it working easily in our Acceptation environment but not in production. Thx for putting me in the right direction!
November 15, 2016 at 2:04 pm
Glad I could help, and thank you for reporting back. This can help others who stumble upon this thread.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply