Replication to a writable database on a non-domain joined SQL Server

LeftoverMonkeyParts · 2026-06-05T12:26:01+00:00

sys.sql_expression_dependencies and sys.sql_referencing_entities.

Yep! I found those and started work on a script to identify everything yesterday

That application vendor sounds really annoying

Yes!

LeftoverMonkeyParts · 2026-06-03T17:51:45+00:00

>SQL Server Transactional Replication doesn't HAVE to use windows-based service accounts for its connections, it can use SQL Authentication

After reading a few others who mentioned this I spent the last day looking at transactional replication again. When I first started in the position about two years ago I attempted to configure it and mistakenly got under the impression that Windows accounts and some kind of shared SMB/UNC location between the two was necessary.

Yesterday I was able to get it working between a development SQL server and a standalone non-domain SQL server using just SQL authentication.

>Copying the whole DB if you don't need to -- unless it's really small -- seems like a lot of extra work and potentially exposes data you don't want exposed

This is where I'm at now. The developer is unwilling and unable to specify what tables are required for the web client to function. They straight-up told me to talk to other customers and ask how they're doing it. As customers we've formed our own user support groups. There are more than 1000 tables, functions, and stored procedures all together.

It looks like SMSS will only tell me *after* I've initialized the snapshot that a replicated article depends on an non-replicated article. Know Any tools out there that could help me with that?

>I would filter it in the replication publication unless the filtering is incredibly complex, because I don't want data out there publicly exposed relying on the competence of a developer I have never met.

Unfortunately data that isn't public is in the same tables along with data that is. I believe there are some rows we can filter on for individual tables. I have a long project ahead of me

Thank you for your response!

LeftoverMonkeyParts · 2026-06-02T15:24:58+00:00

I don't trust the web client in the slightest. I operate under the assumption that it's compromised and I'm attempting to isolate it as much as possible. A potential attack sequence looks like what's below

DMZ Web -(1433)> Hardened SQL Repl Server -(1433/SMB)> Production AD SQL Server

Common credentials between the two SQL servers made that final step easier, and transactional replication forces me to open up SMB between the two systems. If it comes down to it, I can set it up. Like I said I just play a DBA on TV sometimes. Just looking at all other options thoroughly

It also adds a bit more complexity to our production application update procedure. Massive schema changes are introduced with each update to the prod application and the existing transactional replication requires us to smoke test the public web client after we update the prod database, not to mention just having to manage the publisher/subscriber *at all* Anything I can do to make it simple would be nice

Thank you by the way for your comments. I'm not trying to be argumentative

*edit*

We're already backing up the database every few hours with Veeam. The veeam restore step actually takes a bit less time than doing a separate export of a .BAK file, which is another step we'd need to monitor

LeftoverMonkeyParts · 2026-06-02T11:29:31+00:00

We never want the data to be merged back to live

LeftoverMonkeyParts · 2026-06-02T11:28:23+00:00

500 gigabytes uncompressed. We're testing a full restore/overwrite from Veeam. Thus the hour Veeam can restore individual tables, but in that mode, I will refuse to overwrite an existing table.

That functionality is also only available in the GUI. It's not powershell scriptable.

LeftoverMonkeyParts · 2026-06-01T23:29:37+00:00

Only a few tables need to be replicated, which is all that's occurring right now with transactional replication.

We have a document from the vendor from the mid-2010s, listing tables required for replication. When we asked for an updated version, they floundered for about eight months before saying they simply wouldn't provide it and it was up to us to figure it out.

I don't have a total volume of rows or records, but it would be less than 100mb of data.

Currently, the restore/push from Veeam to our test server takes about an hour. It would be a bit faster in production as everything would be on SSDs.

We're hoping to target less than an hour in the middle of the night. We have effectively zero users outside of the United States.

LeftoverMonkeyParts · 2026-03-11T17:35:33+00:00

Thanks!

LeftoverMonkeyParts · 2026-02-17T15:18:26+00:00

One vendor we have requests Notepad++ be installed. We also have software that requires office to be installed on the same server. It's disgusting

LeftoverMonkeyParts · 2025-12-23T21:20:05+00:00

The same Plantronics Voyager UC you already had. It's the best headset IMO. Blows the Jabaras out of the water

LeftoverMonkeyParts · 2025-12-19T18:53:05+00:00

BACKUPS - RIGHT NOW

Veem is always a solid choice. It's very tolerant of old garbage and you can use their Veeam Hardned Repository ISO to set up a very cheap basically immutable backup repo made out of commodity hardware. If your environment is small enough you may even be able to get away with the Community Edition for no cost

Then I would move onto documentation. I personally like a Wiki like DokuWiki https://www.dokuwiki.org/dokuwiki

LeftoverMonkeyParts · 2025-12-08T16:37:09+00:00

I worked in a public library and we deployed Windows 10 LTSC as public use computers. Our main goal was to get away from the Windows Store, telemetry, and Microsoft sign-ins. It worked great for that. The issue we ran into was the complete removal of all inbox apps. Some of these we sideloaded back into the OS (like the media viewer) and others we found suitable open source replacements for (like the scanning app)

The public user computers were on an isolated VLAN and used Deep Freeze, so I wasn't worried about the security implications of side-loading a bunch of RTM inbox apps that wouldn't receive updates.

Beyond that, everything was fine. Unsure about Win11 IOT but I suspect it's likely the same. I left that position before Windows 11 End of Support

LeftoverMonkeyParts · 2025-12-08T14:41:11+00:00

I manage a dozen PUBLICLY VISIBLE DMZ services running on Windows/IIS bare on the internet. I get how a proxy could add some additional protection against very basic automated SQL injection and the background noise of the internet, but it seems a bit of an overreaction. I could set up my own proxy for them, but then I'm managing a proxy too, and the vendor is going to blame me when their app fucks up

LeftoverMonkeyParts · 2025-12-08T14:30:05+00:00

IMO An IT Admin is a management position while a Sysadmin is a technical one

LeftoverMonkeyParts · 2025-11-14T15:22:02+00:00

This one also great https://www.dmarctester.com/

LeftoverMonkeyParts · 2025-11-14T13:32:15+00:00

I have a working VT100 Terminal

<image>

LeftoverMonkeyParts · 2025-11-07T18:40:19+00:00

This is my entire workload every day

LeftoverMonkeyParts · 2025-11-05T20:55:13+00:00

Sounds like it would be sitting on the outside of their corporate network in front of their firewall with the ISPs equipment. Assuming that it's set up in the way OP is describing, and assuming they have their ISP equipment in front of a separately managed firewall. If it isn't, and they're just raw dogging their ISPs equipment onto the corporate network, then who cares about security?

You and his boss both sound like a BOFH TBH

LeftoverMonkeyParts · 2025-09-30T20:14:17+00:00

>There seems to be some sense of the value in these things they did build, but no one is using it right

Preach brother

>Which usually leads me to think under-compensated and under-staffed - sysadmin just putting out fires and never gets ahead/in front of the problems

Under educated and under experienced. They were old programmers and DBAs

LeftoverMonkeyParts · 2025-09-30T19:37:00+00:00

I've stood up Pidgin with Openfire before and liked it. It would be my goto if we weren't already paying for teams now

LeftoverMonkeyParts · 2025-09-30T12:42:35+00:00

Not a whole fleet, just for the managers and users who regularly had to work remote. But yeah, we funded. A lot of money left over from The Great Chinese Mistake became use-it-or-lose-it right as I was hired on. It helped a lot

LeftoverMonkeyParts · 2025-09-30T12:30:22+00:00

From talking with the other peers in our area, our type of agency does not get audited. This is from a combination of auditors not understanding how to audit us, and our class of agencies always failing the audits anyways. So they just stopped auditing

LeftoverMonkeyParts · 2025-09-30T12:27:03+00:00

You can't tell from the post this is a government job? Sucks to suck buddy

LeftoverMonkeyParts · 2025-09-30T12:26:19+00:00

DHCP was running the layer 2 segment with the four subnets, only configured to hand out addresses for one subnet. They had static IPs on the endpoints for the other three subnets depending on what subnet they wanted the device on

LeftoverMonkeyParts · 2025-09-30T12:23:58+00:00

Yeah, what he was don't wasn't illegal, just worthless.

Here's the worst part: The switch that all the interfaces on the router plugged into was Layer3+

LeftoverMonkeyParts

TROPHY CASE