March 2, 2022 at 12:00 am
Comments posted to this topic are about the item Can We Please Stop Sending Passwords Over the Wire?
March 2, 2022 at 12:14 pm
Even the best protocol can be poised with poor practices.
March 2, 2022 at 1:20 pm
I'll make an educated guess about why Microsoft has not changed this. First, the design for SQL Authentication probably originated with Sybase in the early 1990s. It was not a bad design pre-Internet. It has been improved to support TLS since those early days.
Second, an OAuth type implementation, as suggested, requires another server and a lot of complexity be added to the product. I would recommend that Microsoft consider that approach as a third option for authentication, not as a replacement to SQL Authentication.
Microsoft desires backward support for SQL Server and all the community products that use it. Changing the way SQL Authentication works, even adding a third option, risks breaking products that have co-existed with SQL Server for decades.
The OAuth approach requires integration with an Identity and Access Management system and a Secure Token Server. Either Microsoft re-invents that wheel and creates a new IAM to install with SQL Server, or allows SQL Server Admins to integrate with an existing one. Of course either approach entails a lot of risks and potential problems for a product that has been very stable for decades.
Ultimately, Microsoft already provides Windows Authentication, which uses more robust security and an external IAM (Active Directory) for authentication. So if SQL Server Admins don't feel secure with SQL Authentication, they don't need to use it. In fact, Microsoft has it disabled by default and Microsoft recommends using Windows Authentication.
Given that Windows Authentication is already the recommended and default option, I doubt Microsoft will ever work to change SQL Authentication.
March 2, 2022 at 4:37 pm
I have a question that I hope is appropriate for this page. I read the article. The article it lists reasons when SQL Authentication is required, but I'm not sure it addressed my agency's situation? My agency currently uses only desktop applications which connect to our SQL Server databases via Windows Authentication. Staff working from home use VPN. So, they are connected to our domain and using Windows Authentication is easy. Of course, we are considering making part or all our applications be browser-based with the idea that staff would be able to use the applications in other places and without having to use a VPN. But I'm confused about how security/authentication works in those situations.
Questions:
2. What about phone apps? We may want to create some phone apps which connect to our SQL Server databases. Can phone apps use Windows Authentication?
3. What about non-staff, ie users outside our agency? We have some features that we may want to open to the public. I don't think we can require the public to use a Windows system, so maybe I already know the answer to this. Surely we can't use Windows Authentication for when the public is connecting? I don't even see how using Windows Authentication would work for any part of the public who is using Windows and trying to use one of our applications. When I set up a login using Windows Authentication, I'm connecting back to our local Active Directory groups on our domain. The public wouldn't be on our domain. I'm so confused. I don't see how this isn't a common requirement for using SQL Authentication. (And if SQL Authentication is not secure because the password is being passed over the "wire", should my agency *not* have any browser-based applications? Our databases have HIPPA data on them...)
4. If we can't use Windows Authentication for any of the reasons mentioned above, then I presume we have to design a system/feature which manages user accounts and gives the users ways to reset passwords, etc. Does anyone have any suggested resources for setting that all up? Are there recommended commercial packages or do companies typically do that with home-grown systems? (One thing I love about Windows Authentication is that our Help Desk can manage accounts and passwords and developers don't have to worry about any of that.)
I apologize if these questions are just too basic and should be something I figure out on my own. You can ignore this post if so.
March 2, 2022 at 5:02 pm
A bit more on my thoughts:
While my agency may be getting more serious about producing browser-based applications, I've had these questions for many years. Why post now and here? I got triggered reading this article. I was under the impression that with browser-based applications, we would have to use SQL Authentication. However, I had thought that using SQL Authentication would just be painful/time consuming to set up a system to manage accounts. I had not thought that it would be less secure! This is scary. And it seemed to me that there would be a lot more situations than mentioned in the article when SQL Authentication would be required. (See my previous post.) But I may not not understand browser-based apps enough and/or the authentication methods enough to have a valid reaction.
----------------
I also wanted to thank Rob-792003 for his post, which was helpful to me in understanding the complexity of the situation and potential reasons why SQL Authentication might be the way it is today.
March 2, 2022 at 6:18 pm
Maxtardiveau,
I agree with what you’re saying. When I worked at Dell Computers, we presented a similar issue to Microsoft in relation to the Microsoft Office suite. Their response was “it’s not economically justified to make such a change”. In other words, they didn’t want to shell out the funds to resolve the issue, let the users suffer. That was back in 1999, today the issue still goes un-resolved.
Corporate greed or justified neglect. I lean a little more toward the corporate greed answer. They constantly put the bottom dollar over the safety and security of personal data.
I have three programs that are Microsoft products, I only use them on a Windows system because there just is not a Linux version available. Otherwise, I'm all Linux.
But that's just my 2 cents worth.
Aubrey W Love
aka: PailWriter
https://www.aubreywlove.com/
March 2, 2022 at 6:44 pm
JJB, your intuitions are correct. You won't be able to use Windows Authentication to easily let the SQL Server database determine the permissions for your users. In the world of web apps, REST APIs, and phone apps, that is not a viable option. But, you still have a choice to make between SQL Auth or Windows Auth. Typically, all the apps on client devices, whether those are web apps or phone apps, will communicate with a Web Server app, which could be a REST API. It is that app, the one running on the web server, that makes the connection to the database; therefore all users going through the app will connect to the database using the same credentials. As far as SQL Server is concerned there is just one user - the web app (or REST API). That web app still needs a connection string and that connection string will either use Windows Authentication or SQL Authentication. If you use SQL Auth, you have to store a login/pwd to build the connection string, so you have the hassle of securing those credentials. If you use Windows Auth, then the connection to SQL Server will be made using the permissions assigned to the account under which the Web App runs. Every web app that runs logs into the operating system; so you can use a built-in account or you can create a custom account in the OS to be used by the web app to log in to the OS. If you take this approach, you will put that login that the web app uses to log in to the OS into an Active Directory group, and map that Active Directory group to a role in SQL Server just like you probably do now.
So, in the end that is all pretty easy to set up and manage. However, you need to remember that all users connect to the database using the same credentials. Therefore, the application needs to have logic in it to restrict actions. Typically, your application uses roles unique to the application that allow the people in the Manager role to do things that people in the EveryDayUser role can't. Those roles don't matter to SQL Server, because it is giving all users the same permissions based on the Web App.
Of course, you now need to map users to roles. This is probably best done by using an OAuth/SAML provider to outsource the application login. When users open your web app or phone app, the app redirects them to a login page that comes from your OAuth provider (which could be AWS IAM or Azure IAM, or OKTA, or many others - you can create your own IAM but I very much recommend against it). The OAuth provider gets their login and password and validates the user, possibly against your Active Directory, and sends a token back to the application to let the application know what roles the user is part of; which your app then uses to enable/disable features.
So, carrying it a little further. If you use a good OAuth provider, that integrates with your Active Directory, your end users could use their AD credentials to logon through OAuth and get a token. In addition, those tokens can be securely cached on the client device and set to expire and also auto-renew. This means that when a user goes to log in to your web app or phone app, they don't get prompted for a password every time because it uses the cached credentials from a previous login - just like facebook and gmail do when you go to their sites.
There are many variations, but I think what I outlined above is the most common approach.
March 2, 2022 at 7:10 pm
Thank you Rob-792003!!!! I will be reading your post multiple times to make sure I understand it. 🙂 Thank you!!
March 3, 2022 at 2:48 pm
Yes, at a minimum developers should set "Encrypt=true" property in client connection string, and even better the DBA should install a proper SSL certificate in SQL Server and set option to enforce all client connections to use encryption. It's essentially the same concept as HTTPS connections on the web.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 14, 2022 at 1:29 am
Well, ultimately, Microsoft is a business, and a business needs to make money. We all have to make trade-offs. It's just that this particular trade-off seems short-sighted. It's the kind of weakness that can bite you really bad.
Yes, if you do everything right, this is not a problem, but good engineering always thinks a couple of steps ahead, and it's obvious (to me at least) that it would be very easy -- almost unavoidable -- to forget or skip one step, and voila! Your database is at risk, and so are your database passwords.
I was surprised to discover this because SQL Server is a really good database, and Microsoft has spent a lot of money on security features. Surely this could be addressed without breaking the bank.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply