April 1, 2018 at 10:52 am
ed.white - Friday, March 30, 2018 1:42 PMI'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.
I'm going to have to remember this, too.
Kindest Regards, Rod Connect with me on LinkedIn.
April 1, 2018 at 3:38 pm
Rod at work - Sunday, April 1, 2018 10:52 AMed.white - Friday, March 30, 2018 1:42 PMI'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.type your message
I'm going to have to remember this, too.
Does this suggest that there is an opportunity to revisit the forest set up to see if the timeout problem can be engineered out of the solution? If SQL Server is getting problems with a 60 second timeout then that timeout might be bugging the hell out of someone using other systems depending on the cross forest arrangement. Possibly an opportunity to kill two birds with one stone?
April 1, 2018 at 7:48 pm
Rod at work - Sunday, April 1, 2018 10:51 AMPeter 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.
It's not that you _can't_ use Azure AD. It works for SSMS and PowerShell and some other things, but if you try to go AD-only SQL Ops Studio won't work, nor will quite a few 3rd party tools. I don't think that should be a show-stopper, but it is annoying at times to need a SQL account to use some tools when your main permissions are set for an AD account. Just means setting up two accounts. :/
April 2, 2018 at 7:50 am
One reason for SQL Authentication over AD is when your front end web servers are located in a DMZ. Adding this server to the domain can expose your organizations domain to any who visit your site and is definitely a much larger security risk than a SQL User account. There are various methods used for encrypting the connection string, not sure how secure that would be as the code decrypting the connection string resides on the same web server.
Another reason is applications that are not AD integrated, some even put out by Microsoft related to accounting. For some really good reasons IT staff is not qualified to determine a user access inside your companies accounting system since CFO's are held legally responsible for that data. In many of these cases the accounting system manages the users in SQL and security is maintained inside the accounting application.
April 2, 2018 at 8:25 am
Rod at work - Sunday, April 1, 2018 10:43 AMbkubicek - 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.
Yes, for sure it is possible to pass along the user accessing the UI. In .net you can do this: WindowsIdentity.GetCurrent().Name
We use this in some of our WPF applications to record who made the change.
Ben
April 2, 2018 at 8:38 am
bkubicek - Monday, April 2, 2018 8:25 AMRod at work - Sunday, April 1, 2018 10:43 AMbkubicek - 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.
Yes, for sure it is possible to pass along the user accessing the UI. In .net you can do this: WindowsIdentity.GetCurrent().Name
We use this in some of our WPF applications to record who made the change.Ben
Yes, we've done the same thing in our WPF at my old job. That works well. I'm realizing that it is going to be a cultural change to try to get them to use SQL users for middle-tier connecting to the databases. I think, though, if I approach it with the other ideas, such as not having the server in the DMZ be in our AD domain, that will help sell it.
Kindest Regards, Rod Connect with me on LinkedIn.
April 3, 2018 at 4:13 am
Eric M Russell - Friday, March 30, 2018 7:12 AMCreating a linked server connection to another domain.
And then we come to genuine reasons why a linked server should be used. For me, they should be pretty much a last resort, the main use cases I've seen for them IRL would appear to be opening vast security holes and writing utter garbage to generate utterly ridiculous performance issues.
While they clearly have their uses, and can - obviously - be used competently if properly thought through, they're mostly a huge red flag in the real world.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 3, 2018 at 4:21 am
bkubicek - Friday, March 30, 2018 8:10 AMlmalatesta - Friday, March 30, 2018 8:04 AMI'm a bit new to the security bits of SQL Server administration but I thought that SQL Authentication mode could be forced to use SSL encryption.So even if you force SSL that is just the connection between the user's computer and the sql server. In most cases the connection string is hard coded in a config file someplace on the user's computer. There isn't really anything keep the user or someone else from opening up the config file in notepad and see the username and password to access the SQL server.
Ben
In that case you should be using protected configuration. Of course if your developers are that averse to giving a flying one about security, you won't be, but it is possible.
Protected Configuration
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 4, 2018 at 2:23 am
If your app uses the AD account to determine the user's role in the application (no login and password to enter) and an encrypted config file (SQL Authentication) to connect to the database, the user cannot even read the data via excel, etc. What is more secure than that? The added benefit is that a user can be the admin of the system.
5ilverFox
Consulting DBA / Developer
South Africa
April 4, 2018 at 8:38 am
Japie Botma - Wednesday, April 4, 2018 2:23 AMIf your app uses the AD account to determine the user's role in the application (no login and password to enter) and an encrypted config file (SQL Authentication) to connect to the database, the user cannot even read the data via excel, etc. What is more secure than that? The added benefit is that a user can be the admin of the system.
I think the point here is the management of AD accounts vs SQL logins and the responsible parties. Using both approaches, I can say that if the environment allows for it, AD is a better way of managing access to SQL Server. I acknowledge that not all situations can accommodate. I am in the same boat, and use mixed mode. I would encourage others to use AD as much as possible, if possible, over SQL Authentication, for sure.
April 9, 2018 at 1:43 pm
I've been trying to promote Windows authentication on my new job, but it's a network with a lot of public-facing web apps, and all the public web servers are in the DMZ, and they tell me they can't put those in the domain because it opens up ports in the firewall! So we have usernames and passwords in plain text in config files! (At least I got permission to remove the sa passwords and use specific usernames for each site instead.)
I was ttaught differently in my previous job, that EVERYTHING should be in AD (for password encryption), but I'm new here and have little influence in that area. Can the community help me, with some authoritative evidence that computers in the DMZ should be in AD, even though it means opening up some specific ports?
Thanks!
April 10, 2018 at 9:53 am
We have an Enterprise Service Bus system running as a Windows service under the local system account. When this system connects to a database because someone of the finance department requests information, I would like the system to login with a user account from the finance department. If a user from the HR department requests information I would like the system to login with a user account from the HR department.
I don't know how to do this without using SQL Server logins.
Kees
April 10, 2018 at 1:10 pm
There is nothing wrong having to use SQL logins to do what you want to do. I also misread this post as : Do NOT use SQL logins, do not even switch mixed mode on. They are actually talking about maintaining SQL Logins per user vs using AD accounts. In your case you will create your SQL logins once off, no need to maintain it. I don't know your reasons, but I would question why you would want to use different logins per department. A different login per database might make more sense.
5ilverFox
Consulting DBA / Developer
South Africa
April 11, 2018 at 2:08 am
Japie Botma - Tuesday, April 10, 2018 1:10 PMThere is nothing wrong having to use SQL logins to do what you want to do. I also misread this post as : Do NOT use SQL logins, do not even switch mixed mode on. They are actually talking about maintaining SQL Logins per user vs using AD accounts. In your case you will create your SQL logins once off, no need to maintain it. I don't know your reasons, but I would question why you would want to use different logins per department. A different login per database might make more sense.
In case of the enterprise service bus a connection per database could be sufficient. When someone is using a reporting tool, the request does not come from another database, that's why I mentioned the department.
April 11, 2018 at 7:57 am
There is an alarming issue when using SQL Server authentication that many DBAs don't consider. Keep in mind that once an application or user logs in, there is nothing preventing them from changing the account password using ALTER LOGIN statement and thus blocking any other users from logging in. An account doesn't need sysadmin membership to change it's own password.
ALTER LOGIN TestApp WITH
OLD_PASSWORD = 'TheOriginalPassword'
PASSWORD = 'HaHaHa@ImSoEvil';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply