@Charlieface Interesting about connection pooling. Not really sure I buy the argument about managing AD Users vs managing any other authentication account though. Same amount of accounts either way. But again, most of my work has been internal applications where everyone is on the same Windows domain and has AD accounts to begin with anyway.
@J.D. Not when you have to pay licencing costs per user and speak to your IT admins every time you want to change something. AD is very heavy for use on a public facing server. Yes it can certainly make sense to use AD on internal servers.
Also sounds like extra work and redundant to have to manage security for each of those individual accounts, possibly in multiple places (each application), without the ability to create AD Groups. (Though I'm sure depending on the authentication method of choice, there's an equivalent option.)
@Charlieface I don't believe we have to pay licensing costs per user in our domain's AD. But there's a reason the word licensing is 🙊
@ErikDarling Then again you got no idea how to play rugby either
@J.D. Depends if you employ them or not. If yes you might be able to get away with just Device CALs, if not you need a very expensive External Connector Licence. And that's if you have on-prem, if you have Azure AD I think it gets worse.
@J.D. That's what OAuth and SAML etc are for. Authentication is done in one place, that authorizes you on the webserver. Direct SQL access is not allowed anyway, so the applications all use the same login, or one login per application.
@J.D. Look, the English in general are not supposed to actually be good at anything, we are only good at looking down at someone else for being worse than us.
@Charlieface I understand what you mean for authentication to connect to the web server, but how do you easily differentiate what users have access to which database functions and data (e.g. based on either database security, Row-Level Security, or both) once they have access to the web application. How is that logic implemented, and cleanly maintainable and re-useable for multiple apps and reports?
@J.D. Erm, pass in a @user parameter maybe? Or use SESSION_CONTEXT and RLS if you have to. The webserver is what gates access to everything, so there's no reason why server-side code cannot do this securely.
I guess depends if you trust the web devs to do security properly or not.
Sure true. So that handles RLS, but we all know RLS isn't really a secure security feature. So what about regular database security?
E.g. at my company, we have multiple related apps that control data in the same Schema. "Admin" level users have read and write access to that Schema. "Regular" users only have read access. So these "Admin" users can modify the data from multiple applications. How would one implement a security model based on the same account making the database connection for all apps for all users?
And those "Admin" users are managed by an AD Group that's mapped to the appropriate permissions in the SQL Server instance. So to add or remove someone to those permissions, for all related apps, is just adding or removing them from an AD Group.
@J.D. RLS is not a security feature in the sense that it can be defeated using side-channel attacks. There are ways around that, primarily by using fixed timers for failed requests. AD doesn't help you for this, permissions are column or object level only.
@J.D. Either write the applications to use a common data access library, or use procedures, again with a @user parameter.
You just need to implement your desired security within your application. Ultimately, if you can't rely on webdevs to do the right thing you may as well pack up and go home.
@Charlieface agreed about RLS, which is why I'm asking from a regular database security standpoint. AD is one way to help manage things though, e.g. with groups that can be directly mapped to database permissions natively in SQL Server.
@Charlieface Doing this means not using native database security in SQL Server then though. Seems silly to me, but I'm always wrong anyway. 🤷♂️
AD groups work well when you want to limit access to direct SQL queries across your org. It's a heavyweight solution for simple applications, often where there is no AD in the first place.
You seem to assume everyone has AD, or that everyone who has AD knows how to manage it properly, or that licencing is never an issue. None of these are true.
Then we fought two WWs, at which the Americans lent us dough and watched us do the fighting, then turned up at the end to claim all the credit and make us bankrupt. Hmmm.
@Charlieface it's just the world I'm used to. But I'm not saying AD necessarily needs to be the vessel that organizes security (not sure what else it would be though). More so, it's a little eye opening to me that people don't normally (or it's not recommended even?) to utilize the native database security features of SQL Server?
Even when I worked for the SAAS FinTech company, their external software was still based on trusted connections and AD security. Installs required on-prem databases for the application though. But the same could still be true even with cloud hosted database instances too.
@J.D. I guess people don't recommend to allow direct SQL access anymore, so the point is a bit moot. SQL permissions work well for something like a data analyst job in a big firm, where they're writing custom SQL all day long and you need tight security around who can query what columns and rows. But a public facing server for some webapp? Nah it's just the wrong tool.
Look I don't know, I'm just repeating what I've seen online and in the wild, and my own thought process on the same questions you have. Ask the experts I s'pose
@Charlieface Well it's not a question of direct SQL access per se. We use an API to interface all of our apps to our databases, but we still impersonate the executing user to establish the database connection accordingly, depending on the function being called.
@Charlieface heh no doubt, sorry if it sounds like I'm grilling ya. I'm asking sincerely because I really don't know what's actually right, only know what I'm used to, and I think you're a pretty smart person so I'm earnestly interested in your input lol.
As mentioned by others, you need to use table aliases on the columns if you have multiple with the same name. You should really always add the table aliases anyway.
But your query can be significantly simplified, by using conditional aggregation.
SELECT
i.owneridname,
COUNT(i.svb_contactname)...
It's the same with all the invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause nonsense: you honestly couldn't get a clearer error message
Btw the other thing I'm not a fan of with a single account (e.g. in IIS) being the gatekeeper to the database, is it probably possesses more permissions than the individual users would typically. So now your database access attack vector centers around that account. I suppose minimally one would use a different account per web app / site, but it's still likely more broadly provisioned than individual users would be, and less obfuscated.
Oh by the way: another issue with using AD for websites is that the user needs to be able to do Kerberos direct from their browser, which primarily only works from Windows, and needs line-of-sight to a DC (bad idea) or a KDC proxy (slightly less bad). I suppose if you have a completely walled off domain it wouldn't matter, still gives me shivers though. I'm a sysadmin for some of my day, and I can tell you I really wouldn't approve that setup.
I'm not a fan of Kerberos to begin with, it's been a pain to utilize correctly. But we are stuck abiding by it.
But again, I'm not necessarily advocating for AD, more so just surprised there is no alternative should one want to use native SQL Server database security.
@J.D. IME pretty impossible to use Kerberos incorrectly. It either works or it doesn't (usually the case). AD itself on the other hand is ridiculously easy to mess up: there are that many ways to give attackers the keys to the kingdom it's frightening.
The amount of times I've seen people trying to protect against a domain admin and I'm like: you do realize they can undo whatever you're doing in about 5 seconds.
@Charlieface most sysadmin types use PowerShell to automagick all the things and those scripts often require unchecked elevated access to manage whatever objects
I work in a small biz so makes little difference, but I know what you mean.
Problem is it's very hard to give Just Enough Access to admins without giving the keys to the kingdom, so most orgs just do that instead and rely on their admins to not mess things up.
It's getting better: fine-grained control is very good in the cloud, and even on-prem a lot of stuff now has detailed permissions.
Something on a different tangent that always bothered me: why do DBAs always write heavy procedural (imperative) code in T-SQL, given it's one of the worst languages in which to do so? Like Ola's gigantic backup scripts, which could have been written far better in a dozen other languages.
Obviously the database queries themselves need to go to SQL, but why write all the heavy logic in T-SQL?
Maybe they should learn? It just seems a shame that so much time is wasted on bashing T-SQL into shape, and none on learning anything new. For eaxmple I seem to recall we had a whopping argument re error handling, I'm in favour of just putting XACT_ABORT ON at the top and bailing out completely.
@Charlieface I don’t know. If I had to learn C# to do something it’d take me a long time to even get up and running and I’d probably screw a thousand pooches along the way to a solution
@ErikDarling I honestly don't recall this. Unfortunately I got pulled off working on that project shortly after we talked and then back onto it months later, which is why there was a large gap in time, perhaps me forgetting what we discussed.
@Charlieface also I’m not smart enough about error handling to argue with anyone about it. Most of the “handling” I do is purely to catch the dynamic SQL errors.
Not sure how that'd work. But you can make an indexed view then query it in a TVF using NOEXPAND, which might be a good idea anyway. The WITH CHECK OPTION was the only other thing I could think of, but who uses that?
@ErikDarling The ones that really make me laugh are the xp_cmdshell ones. It's not the security issues that bother me, it's just the whole "let's call out to cmd.exe to execute bcp.exe to read data from SQL and write it out somewhere just so we can make a CSV file" I mean why not just use Invoke-DbaQuery ... | Out-Csv ... one line of Powershell.
People just picking the wrong tool for the job. SQL is great for querying, nothing else. it's not a scripting language, don't misuse it.
Something on a different tangent that always bothered me: why do DBAs always write heavy procedural (imperative) code in T-SQL, given it's one of the worst languages in which to do so? Like Ola's gigantic backup scripts, which could have been written far better in a dozen other languages.
Much of the time, T-SQL is indeed the wrong choice. Aside from what Erik said about it being the only language they know, there are some other considerations.
For one thing, you already have a database, so you might as well use it. If you put the routines there, you know they'll be available, easily editable, backed up, schedulable via SQL Agent and so on. That doesn't make it the right choice all the time, but they are valid considerations.
And when something goes wrong, you only need to look in one place.
It's pretty shocking SQL Server doesn't allow better access to other languages from within the product.
There's really very little reason modules have to be written in T-SQL.
CREATE PROCEDURE <name> LANGUAGE C# AS really ought to be a thing.
Another point to consider is that your audience is DBAs who also use T-SQL. If they run into an issue they’re more likely to be able to debug and fix it, or make tweaks specific to their environments.
I wonder when Ola will rewrite his scripts in PowerShell or cmd/batch.
Neither are supported for SQL Server on Linux but no one uses that.
Also wasn't there some weirdness around the PS thing?
Oh right
> There are two SQL Server PowerShell modules; SqlServer and SQLPS. The SqlServer module is the current PowerShell module to use. The SQLPS module is included with the SQL Server installation (for backward compatibility) but is no longer updated.
> Starting with SQL Server 2019, you can disable SQLPS. On the first line of a job step of the type PowerShell you can add #NOSQLPS, which stops the SQL Agent from auto-loading the SQLPS module. Now your SQL Agent Job runs the version of PowerShell installed on the machine, and then you can use any other PowerShell module you like.
I have two containers. Both are identical. Both are launched using the same docker-compose.yml file on two different servers. Both have the same data. Exactly the same (one is backed up and restored in the other one).
Yet I get different sorting for the same query.
This is my query:
SELECT `i`.`I...
@ErikDarling It's interesting what things people will upvote. The simplest of answers of mine gathered many more upvotes than some of the more involved ones.
Also, seems to be a bit of a bug in the chat - at least on mobile, perhaps some kind of race condition. Whenever I edit a message immediately after I send it, it dupes the message in the chat.
Also also, that question's duplicate Post has also been closed as a duplicate linked to another Post closed as a duplicate, ad nauseam, about 5 times over.
Can I find out why this question was deleted and whether there are changes that could allow it to be undeleted? I have found an answer and would like to post that.
https://dba.stackexchange.com/questions/302471/why-does-access-recalculate-a-stored-query-when-i-copy-it-can-i-tell-it-not-to
As mentioned by others, you need to use table aliases on the columns if you have multiple with the same name. You should really always add the table aliases anyway.
But your query can be significantly simplified, by using conditional aggregation.
SELECT
i.owneridname,
COUNT(i.svb_contactname)...
Can I find out why this question was deleted and whether there are changes that could allow it to be undeleted? I have found an answer and would like to post that.
https://dba.stackexchange.com/questions/302471/why-does-access-recalculate-a-stored-query-when-i-copy-it-can-i-tell-it-not-to
