July 25, 2018 at 2:56 pm
For as long as I've used SQL Server, I've always used it in a way where an application (either Windows or Web) would authenticate against the database using the user's Windows identity. What I guess is called using NTLM.
Now, we're being asked to come up with protocol solutions, in both Windows and Web, that will hit against a database behind our firewall and made available to what I call "known external users". Users who are authenticated against a server in our DMZ, interacting with some service in the DMZ, which performs all of the database actions on the user's behalf. I was first told that I had to use ADFS, to authenticate the known external user. (We're using ADFS on a Windows 2012 Server.) So, I learned how to do that. But now comes the next part and this is harder, Kerberos.
I was told that SQL Server, when not using Windows authentication (NTLM, I believe), may only be accessed using Kerberos. I have spent days trying to find how to use Kerberos programmatically. And I've come up empty. I can find hundreds, perhaps thousands or millions of websites describe how to set up Kerberos; how to configure Kerberos; how to administer Kerberos. None of that helps me. I want to know how can I interact with Kerberos in a application so I can access the database behind the firewall on the user's behalf, as the user. Or if you prefer delegated to the database as the user. How do I do that?
Kindest Regards, Rod Connect with me on LinkedIn.
July 30, 2018 at 4:56 pm
Windows Authentication is Kerberos. ADFS just provides SAML support for authenticating who is connected based on external criteria. The options I can think of are:
1) Use ADFS to authenticate users for access to your DMZ service, then just have the DMZ service run as a specific known Windows user (or connect with an SQL account) and perform all access to the database via that account.
2) Have your DMZ service map the authenticated user onto a internally defined AD account, impersonate that account and then use that impersonation to connect to SQL Server. This requires your DMZ service to be trusted for Kerberos delegation though, since it will need to access network resources whilst under impersonation.
1) is the easiest to implement, but makes auditing and securing objects more difficult as you need to pass through auditing information with each call. 2) Is more complex, but gives you the full weight of integrated security and auditing capabilities.
July 31, 2018 at 11:49 am
Hi Andy,
Thanks for your reply. What makes it so confusing to me is I get things like a SPN (I know of one) and told to use it. But I'm like, "Huh?". I've never even heard of a SPN before - what is it and how do I use it? I've looked it up, so I know what the acronym means, but I still don't know how to use it. And when I read what you wrote in #1, I wonder if I even need to use it.
Kindest Regards, Rod Connect with me on LinkedIn.
July 31, 2018 at 3:07 pm
Rod at work - Tuesday, July 31, 2018 11:49 AMHi Andy,Thanks for your reply. What makes it so confusing to me is I get things like a SPN (I know of one) and told to use it. But I'm like, "Huh?". I've never even heard of a SPN before - what is it and how do I use it? I've looked it up, so I know what the acronym means, but I still don't know how to use it. And when I read what you wrote in #1, I wonder if I even need to use it.
If they tell you to use TCP\IP, you don't really interact with it. You enable it.
SPNs, Kerberos are for allowing credentials to be passed more than one server and you are really just enabling this process. Setting up an SPN and allowing delegation allows you to use Kerberos so credentials (logins) can be passed to another server. If no credentials are passed, you get the error Login failed for NT AUTHORITY\ANONYMOUS LOGON which is called the double hop issue or error. That's one of the more common issue you are trying to work around with SPNs and Kerberos.
It sounds like they only want windows authentication mode for SQL Server. As to the rest with whatever application, the DMZ and AD, everything really depends on how the DMZ is setup for all of this. If you are tasked with that, you would want to go through the following - there are three parts to the article:
Active Directory Domain Services in the Perimeter Network – Part 1
Sue
August 1, 2018 at 9:43 am
Sue_H - Tuesday, July 31, 2018 3:07 PMRod at work - Tuesday, July 31, 2018 11:49 AMHi Andy,Thanks for your reply. What makes it so confusing to me is I get things like a SPN (I know of one) and told to use it. But I'm like, "Huh?". I've never even heard of a SPN before - what is it and how do I use it? I've looked it up, so I know what the acronym means, but I still don't know how to use it. And when I read what you wrote in #1, I wonder if I even need to use it.
If they tell you to use TCP\IP, you don't really interact with it. You enable it.
SPNs, Kerberos are for allowing credentials to be passed more than one server and you are really just enabling this process. Setting up an SPN and allowing delegation allows you to use Kerberos so credentials (logins) can be passed to another server. If no credentials are passed, you get the error Login failed for NT AUTHORITY\ANONYMOUS LOGON which is called the double hop issue or error. That's one of the more common issue you are trying to work around with SPNs and Kerberos.
It sounds like they only want windows authentication mode for SQL Server. As to the rest with whatever application, the DMZ and AD, everything really depends on how the DMZ is setup for all of this. If you are tasked with that, you would want to go through the following - there are three parts to the article:
Active Directory Domain Services in the Perimeter Network – Part 1Sue
WOW, thank you for the link, Sue!
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply