The Heap™ – Consultancy ©®

« first day (5021 days earlier) ← previous day next day → last day (70 days later) »

Sean Gallardy

00:25

Normally we don't let those types in :P

4 hours later…

Paul White

04:13

Wordle 1,202 4/6*

⬛⬛⬛⬛⬛
🟨🟨🟨⬛⬛
🟩🟨⬛🟨🟩
🟩🟩🟩🟩🟩

3 hours later…

ypercubeᵀᴹ

07:34

hey all

Are you qualified to use NULL?

ypercubeᵀᴹ

08:12

@PaulWhite is that Vcore seconds or Vcrore seconds? ;)

4 hours later…

Hannah Vernon

12:35

Wordle 1,202 4/6*

⬛⬛⬛⬛⬛
⬛⬛⬛⬛⬛
⬛🟨⬛🟨⬛
🟩🟩🟩🟩🟩

@ypercubeᵀᴹ hi, it's been a while! How's things?

Josh Darnell

12:49

I just ran into a weird situation. Or at least, I thought it was weird. SQL Server being run by a gMSA. An app on a different computer running as the same gMSA. The app had weird permission problems - like, inexplicably couldn't query any tables in the DB, even though a login and user were created for the gMSA and had db_owner on the DB in question.

I don't think I've ever seen someone trying to connect to SQL Server using the same account that is running SQL Server.

The error I kept seeing was 208 ("Invalid object {table name}").

But I verified all the normal stuff. The app was connected, in the right DB context. The query didn't specify a schema, but the gMSA user's default schema for the DB was correct for that table.

Casing of the table names matched (although I confirmed the DB was in a case-insensitive collation).

I'm at a loss for what else to even check.

Erik Reasonable Rates Darling

13:11

nevermind

Paul White

13:37

@JoshDarnell Is it a member of sysadmin? Default schema is ignored in that case (you get 'dbo').

But name resolution is an absolute nightmare even without that.

Zikato

@JoshDarnell did you trace the tsql callstack? It could happen elsewhere than you expect

straightforwardsql.com/posts/…

Josh Darnell

@PaulWhite Oh, I bet it was! I didn't know that, thanks! Seems like a pretty good lead.

Paul White

I thought it would be madness initially but you're talking about the account SQL Server is running under. I wouldn't be surprised if someone assumed that needed sysadmin.

Josh Darnell

@Zikato Thanks! In this case, the queries are hardcoded (ad hoc) in the application code, and being run one-at-a-time. So there's no stacking involved.

Paul White

@JoshDarnell All questions resolved by consulting the documentation

And for the really curious, an old four-part series on the topic

Thank goodness they hardcoded the raw SQL in an application. That makes everything easier

Josh Darnell

13:50

@PaulWhite Amazing.

@PaulWhite easier 4 devs

Paul White

During development I guess yeah

Josh Darnell

I wonder if SQL Server knows when a login is created that the login is also the account running the service, and it gets implied syadmin, or added to sa automatically.

Paul White

A: Giving server-role to login breaks stored procedures

Grant CONTROL SERVER to the login rather than adding it to the sysadmin role. As documented in Books Online: The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo. CONTROL...

Hannah Vernon

@JoshDarnell I wonder what EXEC sys.xp_logininfo @acctname = N'<gMSA>', @option = 'all'; shows.

Paul White

@JoshDarnell I would certainly hope not. There's no need for the service account to have such high permissions

I can see why people would think so though

Zikato

13:55

@JoshDarnell There still could be. Triggers, views, functions, synonyms

Paul White

Service account permissions documentation

Zikato

You'd see most of the info required for debugging there. Exact code it ran and which portion errored out, login, hostname, and even client PID.

I resolved issues in the past where code didn't deploy to one of the app machines so it was running a different version than the others.

Paul White

I suppose you could try creating a synonym in dbo to point to the table the app needs, assuming no name conflict that ought to work

Or a view of course, but some apps have trouble with those

I'm probably being optimistic that a synonym would cause them less trouble

Josh Darnell

23 mins ago, by Paul White

@JoshDarnell Is it a member of sysadmin? Default schema is ignored in that case (you get 'dbo').

Confirmed.

Paul White

Awesome. Just the sysadmin access then

Josh Darnell

14:02

@PaulWhite The same gMSA is used for SQL Server Agent service, so I wonder if that's why it was in the sysadmin role.

> The per-service SID of the SQL Server Agent service is provisioned as a Database Engine login. The per-service SID login is a member of the sysadmin fixed server role.

I don't really understand that "per-service SID" bit entirely.

Paul White

🤷‍♂️

Zikato

I guess if you have multiple instances on the same machine

Paul White

@JoshDarnell Well, that's news to me. How wonderful

Please ignore all my previous remarks about service accounts. Jesus

Josh Darnell

It's insanity.

Paul White

If it's not possible to change the login the app uses for some reason, you could try the synonym/view workaround

Josh Darnell

14:10

@PaulWhite Thanks! They are requesting a separate account for the app to use. So that should resolve it.

Paul White

Oh, that's a much better idea

Good job it's a current app then

How does one authenticate with a gMSA account briefly?

I assume there's no password as such

Having random apps accidentally running as sysadmin gives me the heebie-jeebies

Josh Darnell

@PaulWhite There's an Active Directory subsystem that handles it. You configure the service / app pool / whatever to "run as" the gMSA account, leaving the password blank. AD is responsible for providing and rotating / maintaining the password.

@PaulWhite Yeah. There is technically a password, but no one ever knows it. AD maintains it, the servers where the gMSA is installed cache it encrypted in the registry (I think).

Paul White

I see, ty

Paul White

15:13

Oh, we're back

stackstatus.net/incidents/b0b7d97b-aa1e-4d57-b5ce-36e2728bcabb

High availability strikes again

Erik Reasonable Rates Darling

16:08

just a heads up for the moderators in here, i've got ~150 posts in which i want to correct my old domain name to my new domain name. i'll be doing them in small chunks until done.

of course if there's a way to bulk update things i'm happy for that to happen too.

Paul White

16:42

@ErikReasonableRatesDarling The SE developers can do that for you. I imagine the process is to use the Contact Us form

A: How to find and/or change a set of hyperlinks on a site?

There exists, internally, a search-and-replace tool for rewriting links. It's very simple; if someone changes their entire URL scheme, it's not much help. But for stuff like this, where we can simply cut out one consistent part of each URL and drop in another... It works pretty well. If you need...

That method doesn't bump posts and is obviously faster than doing it manually

Erik Reasonable Rates Darling

Are votes from post bumps considered cheating

Paul White

No, but dominating the feed page is considered rude

If I were you, I'd fire off a request and wait to see what they say
Editing 150 posts is intern work, not fit for a moderately influential database professional

It's not urgent seeing as the old site 301s to the new

(HTTP 301 = Moved Permanently)

But if you really want to do 5 a day for 30 days, go ahead

Erik Reasonable Rates Darling

I bet I could automate it in OLOPS

Paul White

16:57

Less than one line of PS

Zikato

And then there's a human error of course

I'd trust Erik's manual edits more than SE intern

Paul White

17:21

regex is dangerous & difficult, it's true

Forrest

@PaulWhite That's why I have AI write it for me

Erik Reasonable Rates Darling

17:51

A very helpful Canadian has fixed my URL woes

Zikato

18:17

@Forrest You could just ask me

Forrest

But then I'd have to pay for any tips I offered.

Paul White

18:45

@ErikReasonableRatesDarling What happened

Erik Reasonable Rates Darling

Bertrand the Unmentionable happened

Hannah Vernon

That's his new band name

Bertrand and the Unmentionables.

@PaulWhite I feel like if Erik were to dominate the news feed with 150 posts, they would at least be quality posts

Erik Reasonable Rates Darling

19:07

You may want to read them first

Hannah Vernon

Erik "The Trusted" Darling's posts don't need no reading

Erik Reasonable Rates Darling

That seems to be everyone’s approach

Paul White

@ErikReasonableRatesDarling Ah

J.D.

20:02

@ypercubeᵀᴹ I see you browse Reddit too...

Erik Reasonable Rates Darling

20:52

i've formed a new typo habit, spelling "deadlock" as "deadlick" and it's somewhat upsetting

1 hour later…

Hannah Vernon

22:06

Freudian Lick?

« first day (5021 days earlier) ← previous day next day → last day (70 days later) »

all rooms

Transcript for

Oct2

Oct3

Oct4

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC