March 30, 2018 at 8:19 am
bkubicek - Friday, March 30, 2018 8:07 AMlmalatesta - Friday, March 30, 2018 8:00 AMbkubicek - Friday, March 30, 2018 7:52 AMBobAtDBS - Friday, March 30, 2018 7:20 AMI see your point if you live in a world where all your users are part of your domain. But if you serve up applications that connect to SQL Server from hundreds (in our case thousands) of users who are NOT in your domain, you have two choices. Use SQL Server authentication, or use one (or a few) logins and do your own login and password control, which may or may not be as good or better than what SQL Server offers. I'm open to viewpoints!I am assuming that if you have thousands of users you are talking about a web app? If you are, normally websites running under IIS will have their own application pool. You can set the identity of the application pool to be a network user. Normally, we get our AD guys to create a service account, then all access to the web app happen through this one AD account.
Ben
Why would you assume that it's a web app because there are thousands of users?
In my opinion, if you have thousands of users, it is way easier to manage application updates and controlling data access with a web app. A web server is a lot easier to scale for better performance. Of course, a web app would require network or internet connectivity, but in most cases this is somewhat of a given.
I would guess since you asked the question that you have an application with thousands of users and it is not a web app?
Ben
There are quite a few vertical markets where there are non-web apps with thousands of users spread out across disparate networks connecting to a single server.
Three of my last five jobs have dealt with apps that meet that criteria.
March 30, 2018 at 8:22 am
Why? Sometimes we either want to authenticate without being tied to our AD login, for example this is particularly useful when trying to test out permissions. In some cases we simply can't use AD logins, for example systems that are connecting to SQL Server but don't come through AD or systems that are simply not designed to use AD authentication.
Now the question is why should we be trying to change it? Mixed mode works fine, if you have bad passage word management having an AD account with access compromised isn't any better and potentially far worse than having just a SQL user account compromised.
March 30, 2018 at 8:22 am
To answer several questions:
No, we don't use a web app. Using a web service would certainly isolate SQL Server from the authentication issue, but it would just move the problem to the web service. The exact same non-domain user issue would still be there. You can't have a thousand users, each with their own database all using the same set of credentials.
Yes, we force SSL. Plain text logins and passwords for thousands of users would be quite a security hole.
We don't store credentials in a plain text config file. I can't imagine a worse security practice.
Our users have to type in their password in the application each time they fire it up. We do store the user's login (optional, if they check a box) in that machine's registry in an obfuscated form.
Any more thoughts? I appreciate the thoughts and questions so far.
Student of SQL and Golf, Master of Neither
March 30, 2018 at 8:26 am
I remember starting out with SQL Server 6.5 as well. This was at my previous job. We eventually moved up to SQL 2012. But through all of that we used SQL authentication.
In my current job, with only one exception, all apps I've seen used Windows Authentication.
I would like to make one correction to what you said, Ben. In ASP.NET it is possible to obfuscate the connection strings within the Web.Config files, so even if people could get to it, they'd not be able to read the SQL username and password.
Getting back to the main thread, I've been thinking a lot about this very issue. Right now, without exception, all of our apps, whether Windows or Web based, are 2-tier apps. However, we're contemplating moving to a more modular system architecture of 3-tier, with some sort of service sitting in the middle between the database and the UI, be that whatever. I did this a lot at my old job. I write several WCF components over the years and a few WebAPI services as well. Since it was at my old job where we only used SQL users, then that middle tier had the connection to the database. The UI never touched the database. I'm wondering how using strictly Windows Authentication works in a 3-tier environment?
Kindest Regards, Rod Connect with me on LinkedIn.
March 30, 2018 at 8:29 am
BobAtDBS - Friday, March 30, 2018 8:22 AMTo answer several questions:No, we don't use a web app. Using a web service would certainly isolate SQL Server from the authentication issue, but it would just move the problem to the web service. The exact same non-domain user issue would still be there. You can't have a thousand users, each with their own database all using the same set of credentials.
Yes, we force SSL. Plain text logins and passwords for thousands of users would be quite a security hole.
We don't store credentials in a plain text config file. I can't imagine a worse security practice.
Our users have to type in their password in the application each time they fire it up. We do store the user's login (optional, if they check a box) in that machine's registry in an obfuscated form.
Any more thoughts? I appreciate the thoughts and questions so far.
A web service under IIS would also have an application pool that could be set to an identity of a single network user. Still with thousands of users you would need to make sure it performs well under that load.
So I am guessing the sql user name and password you are using never changes? I suppose as long as the user name and password isn't well know. Still I am guessing it is pretty hard to change.
Ben
March 30, 2018 at 8:32 am
In our organization we have services/apps/users in one domain that require access to SQL Server services in another domain. There is a one way trust between them. Legal handcuffs due to several mergers. Also, AD in both domains is managed by two different organizational teams. So, the red tape that someone must go through to get any AD stuff changed and the wait time to get it done just pushes the DBA's to use mixed mode. Our deliverables cannot be tied to these groups.
March 30, 2018 at 8:34 am
Rod at work - Friday, March 30, 2018 8:26 AMI remember starting out with SQL Server 6.5 as well. This was at my previous job. We eventually moved up to SQL 2012. But through all of that we used SQL authentication.In my current job, with only one exception, all apps I've seen used Windows Authentication.
I would like to make one correction to what you said, Ben. In ASP.NET it is possible to obfuscate the connection strings within the Web.Config files, so even if people could get to it, they'd not be able to read the SQL username and password.
Getting back to the main thread, I've been thinking a lot about this very issue. Right now, without exception, all of our apps, whether Windows or Web based, are 2-tier apps. However, we're contemplating moving to a more modular system architecture of 3-tier, with some sort of service sitting in the middle between the database and the UI, be that whatever. I did this a lot at my old job. I write several WCF components over the years and a few WebAPI services as well. Since it was at my old job where we only used SQL users, then that middle tier had the connection to the database. The UI never touched the database. I'm wondering how using strictly Windows Authentication works in a 3-tier environment?
You have a fair point on using asp.net with SQL username and password, but since it is a web app and there is an application pool, again the identity of the web app can be set to a network user.
If the middle tier is some sort of windows service that is doing the actual connection to the sql db, a windows service has a logon identity which can be passed through to connect to the sql server.
Ben
March 30, 2018 at 8:45 am
We use hiera to store encrypted settings. Reading the hiera eyaml file won't do you any good.
What little AD and Linux experience I have suggests that an app has a special AD user that can traverse a specific AD branch. When the first login attempt is made the user name is copied into the app's own user catalogue. From that point on authentication checks the app repo first then tries to use the AD credentials. If successful then it appears that AD is the primary source of logins. The down side is that a lot of orphanned users build up over time in the app repo as people leave the organisation
March 30, 2018 at 8:59 am
We're small enough that we have no trouble using AD exclusively. One really nice feature of AD is hierarchal security, where assigning a single group to a user has the ability to "ripple" several other permissions. This is especially handy when say, the VP of operations needs all the permissions of a tech plus their own.
It really simplifies and enables complex security requirements in an easy-to-manage way.
Plus it's a single interface for both Windows and SQL Server users. Nice for us lone wolf types.
March 30, 2018 at 9:17 am
roger.plowman - Friday, March 30, 2018 8:59 AMWe're small enough that we have no trouble using AD exclusively. One really nice feature of AD is hierarchal security, where assigning a single group to a user has the ability to "ripple" several other permissions. This is especially handy when say, the VP of operations needs all the permissions of a tech plus their own.
It really simplifies and enables complex security requirements in an easy-to-manage way.
Plus it's a single interface for both Windows and SQL Server users. Nice for us lone wolf types.
I agree AD provides a much better account/resource management process than SQL authentication. Alas, environmental factors prohibit some of us from using it exclusively. Perhaps if AD can be expanded to work in these weird environments in a simple fashion, than people would adopt it more. I try to use AD whenever I can, unfortunately can't get there completely.
March 30, 2018 at 9:26 am
'Mixed mode' because we aren't exclusively a Microsoft shop and/or management isn't willing to re-write a large portion of our code base. I would love to have an environment where AD integrated with all of our systems and we could move away from native SQL accounts. This is one reason why organizations need IT architects to think about these issues and plan for them before they evolve into rigid systems.
March 30, 2018 at 1:25 pm
Well, another concern is if you're using Azure SQL. Yes, there's Azure AD, but a surprising number of tools don't support that. That leaves us with SQL accounts. When we hosted our own SQL Servers, we used Windows Auth exclusively. Now that we use Azure SQL, we use primarily SQL logins just because the tools don't support anything else. Even MS tools are waiting on the core/shared team to add Azure AD support and they can't do anything until that team adds it.
March 30, 2018 at 1:42 pm
I've seen systems where using Windows Auth fails due to Login timeouts set to over a 60 seconds. Mostly due to Cross Forrest setups and Domain Controller issues. SQL Logins fixes this issue.
April 1, 2018 at 10:43 am
bkubicek - Friday, March 30, 2018 8:34 AMRod at work - Friday, March 30, 2018 8:26 AMI remember starting out with SQL Server 6.5 as well. This was at my previous job. We eventually moved up to SQL 2012. But through all of that we used SQL authentication.In my current job, with only one exception, all apps I've seen used Windows Authentication.
I would like to make one correction to what you said, Ben. In ASP.NET it is possible to obfuscate the connection strings within the Web.Config files, so even if people could get to it, they'd not be able to read the SQL username and password.
Getting back to the main thread, I've been thinking a lot about this very issue. Right now, without exception, all of our apps, whether Windows or Web based, are 2-tier apps. However, we're contemplating moving to a more modular system architecture of 3-tier, with some sort of service sitting in the middle between the database and the UI, be that whatever. I did this a lot at my old job. I write several WCF components over the years and a few WebAPI services as well. Since it was at my old job where we only used SQL users, then that middle tier had the connection to the database. The UI never touched the database. I'm wondering how using strictly Windows Authentication works in a 3-tier environment?
You have a fair point on using asp.net with SQL username and password, but since it is a web app and there is an application pool, again the identity of the web app can be set to a network user.
If the middle tier is some sort of windows service that is doing the actual connection to the sql db, a windows service has a logon identity which can be passed through to connect to the sql server.Ben
Ben, is it possible to pass along the Windows user accessing the UI, whether that's Windows or web (intranet) so that some sort of impersonation can occur? When I did this at my old job we had WCF services which ran under one account, and password along the Windows user so that we can keep track of who was accessing the data and when. In one real way, that's ultimately what we want anyway.
Kindest Regards, Rod Connect with me on LinkedIn.
April 1, 2018 at 10:51 am
Peter Schott - Friday, March 30, 2018 1:25 PMWell, another concern is if you're using Azure SQL. Yes, there's Azure AD, but a surprising number of tools don't support that. That leaves us with SQL accounts. When we hosted our own SQL Servers, we used Windows Auth exclusively. Now that we use Azure SQL, we use primarily SQL logins just because the tools don't support anything else. Even MS tools are waiting on the core/shared team to add Azure AD support and they can't do anything until that team adds it.
Interesting. We're considering using Azure more, but given what you say, and since the current thinking is favoring AD for authentication, I would have to conclude the instant they (my management) realize they can't use AD to auth with Azure SQL, that will just put the kibosh on the whole idea of using Azure.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply