« first day (4534 days earlier)      last day (110 days later) » 

12:03 AM
@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.
@Charlieface England seemed to forget how to last Six Nations too. 😉
Jk, I'm just an Ireland fan.
@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.
12:12 AM
Same reason this country is full of curtain-twitchers, and "oh they had a Covid party" whingers.
@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.
@Charlieface you mean an @user parameter, for a procedure driven database implementation?
Normal parameterization. Not sure what you mean, you can pass parameters to ad-hoc batches or ORM generated queries too
12:20 AM
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.
You used to be pretty decent at naval stuff
Finding places
But yes if you have AD for these users then it can make sense.
12:35 AM
Planting flags about the world
But of I-got-your-nose diplomacy
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.
Bit of I-got-your-nose diplomacy
yeah well we're not good at anything else. That's another thing we English are good at: we never forget we won the war
@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
12:45 AM
@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.
@ErikDarling Looks like the ambiguous column question could be simplified out anyway
A: Joining two SELECT queries are not joining into a new table

CharliefaceAs 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)...

Oh I see, yeah. I didn’t pay much attention to the query itself because the formatting gave me a grouch
Bit odd to see someone mystified by the error message after aliasing several times
Life of a hobo I suppose
You have nice formatting, for an Englishman
Most questions which have a compile error mystify me: why can't you just read the *** message? Why did you think it wasn''t ambiguous
@ErikDarling oh shut up :-)
Heh heh heh
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
May 19 at 13:45, by Erik Darling
Apr 9 at 18:21, by Paul White
Reading comprehension being what it is these days
12:59 AM
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.
So many apps need elevated access to function it’s sort of absurd
@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.
Never had a good Kerberos experience
1:11 AM
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.
All those PowerShell scripts running unrestricted
Been trying on and off to get KDC proxy working for RD gateway, so that we can finally switch off NTLM altogether, not having much luck.
Which scripts?
@ErikDarling samesies
@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.
1:20 AM
More Details, More Problems
Takes someone interested in managing all that
Eh anyway, I just make stuff go faster and lament the state of the world
Everything you’ve been talking about is modern Greek to me
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?
Personally, I only use procedures when I need to, and I try to keep the code manageably minimal in length and refactored when reasonably possible.
But then some of my code makes Erik sad and results in complex and poor execution plans, resultantly.
But I also understand you're probably asking more so about people who stuff non-database worthy work into procedures and T-SQL.
@Charlieface because they don’t know other languages
I did a sumptuous amount of dumb things in T-SQL because I could do it quickly and correctly
@J.D. I’m only sore that I told you what a problem was months ago and only listened to Paul about it more recently
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
1:33 AM
@ErikDarling Well to be fair, your solution was to materialize some of the code, but Paul's solution didn't require settling that way lol.
I told you to use an ITVF instead of a view to push predicates past the dense ranks 🫠
Yeah but once you've learnt it you've learnt it. To be honest in your position I'd probably go for Powershell with the DBAtools package
@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.
@ErikDarling I always saw iTVFs as what views always should have been. There's almost nothing you can do with one you can't do with the other.
1:35 AM
@Charlieface I don’t do any DBA tasks regularly enough to bother with that though
@ErikDarling Eh? What else do you do?
@Charlieface we’ll you can’t index them, but views eligible for indexing are unicorns
@Charlieface I do all server analysis and query/index tuning. No backups, no HA/DR, no security. Don’t care for it.
But I’m putting indexed inline functions on my wishlist
@J.D. it’s not that big a deal. Everyone waits for Paul to answer because he’s always right.
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?
I see a lot of people who don’t even use it on constraints
So you know
@ErikDarling heh, that's why I hang around, the universe needs a balance with someone being always wrong.
Also, believe it or not, I'm still trying to wrap up that project, more months later now, because of how frequent project interruptions are for me. :/
1:43 AM
I mean look if god told me to go left and Paul told me to go right I’d probably have to think about it a minute
Fair heh.
@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.
I do agree with you there, but I’m also someone who used to use it to map drives and call cURL to download files and write text files etc.
There are also some companies whose idea of security is to not use dba tools because they think it’s a security risk (also a true story)
I wish they'd completely deprecate sp_OA it's probably the most misused feature set.
@ErikDarling Those are also the ones who make everyone change their passwords every 90 days because "security", oh wait doesn't MS do that?
Best passwords are ones you can remember but v hard to guess, ergo a long complex password which rarely changes.
2:13 AM
@Charlieface I think they gave that up
@Charlieface hate it
5 hours later…
6:57 AM
Wordle 715 2/6*

Wordle 715 4/6*

7:44 AM
6 hours ago, by Charlieface
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.
8:39 AM
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.
2 hours later…
11:06 AM
@PaulWhite Agent allows jobs written in Powershell and cmd/batch. And jobs are backed up, and you can run them using sp_start_job.
11:33 AM
i forget what year that became available
ola's scripts dropped around 2007 or 8
2 hours later…
2:00 PM
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.
Sep 14, 2017 at 1:13, by sp_BlitzErik
i'lll be forever remembered as the man who loved powershell
Jul 29, 2017 at 15:14, by Paul White
New Post: Live Blogging: Erik vs. PowerShell https://www.brentozar.com/archive/2017/07/live-blogging-erik-vs-powershell
Back when tweets used to onebox 😢
1 hour later…
3:44 PM
Why isn't this question closed as dupe already
Q: Why do I get different sorting for the same query on the same data in two identical MariaDB instances?

Ali EXEI 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...

It's just the usual "why is the default sort order not working" question
4:27 PM
@PaulWhite Elon broke chat 😭😭😭
@Charlieface maybe because Jason Doorhees has a popular answer?
5:14 PM
Should've asked GPT
5:37 PM
Maybe I should start answering MySQL questions. Look at all those upvotes.
1 hour later…
6:56 PM
@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.
just because they're longer doesn't mean they're more involved 😀
1 hour later…
8:34 PM
Duplicates were a mistake
8:55 PM
@ErikDarling por que no los dos?
9:19 PM
I mean I’ve been on dba.se a while and I’m definitely not more involved
2 hours later…
11:24 PM
Q: Can I find out why this question was deleted?

NewSitesCan 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


« first day (4534 days earlier)      last day (110 days later) »