Topic: IT Secrets thread!

[PaulF]

SQL is straight forward starting out - though while the fundamentals are the same, some versions are more powerful than others. I.e. T-SQL is Microsoft's version for SQL Server and it's easier to work witth than MySQL in my opinion.

Is your background T-SQL? I find mySql 'tricky' but only because I'm having to translate from T-SQL. Same for the few times I've used Oracle.
For sure there are some features not yet in mySQL but not ones I normally use. Heck even windowing functions send me running for google.

[Claire.]

Where's the neckbeard emoji when I need it?

[Andy @ Allerton!]

Dan. Vba and sql are my bread and butter.
Happy to do a half hour call with you to talk through the size of the data and the kinds of things you are doing. Power query is an option too.
No charge as long as you talk to me about data analysis as I'm hooked on learning ML but never get started.

Anyone on here , maybe Andy, able to help with configuring SQL in azure? I've got the dB running fine and connect as long as I can open the firewall to known IP addresses which is good for the use cases I've encountered so far. But if I want a public one ...   I'm assuming I need an app server with a fixed IP address but I don't see how that improves security . I assume security is the reason I can't allow access from anywhere.

You can change the CIDR Block to 0.0.0.0/0  - but why would you want to do that? Why would you want a public one that's not IP locked?

If you're calling it from 'somewhere' then you only want authorised users or apps accessing a database from a known IP source (Can be a Company VPN or an Azure VPN)

Not done that much with Azure to be honest. I'm mostly AWS and GCP.

People regularly scan and try and attack AWS/GCP/Azure at every single second. They'll be scanning for open ports on every range - thousands of them and if they found an open SQL server then they'd be running password crackers on it 24/7.

Even on our secured open systems it's always interesting to see the Imperva or Cloudwatch logs. The number of declined port scans is scary

Remember that you have private (internal) IPs and Public (External) IPs.

Apps and servers in your VPC will have access to the database indirectly usually.

So you'll have an Internet Access Point which will be pointed at your public subnet - this will have stuff that you want to access from the Web/Anywhere. These will have firewalls and stuff from the Cloud provider and then you can add other stuff.

These will be able to call the private subnet where your application servers reside. These won't be publically accessible.

These in turn can then call the persistance layer where your Database servers etc. are located.

So the environments are airgapped away from your data.

You may provide access for DBAs to access the Database server directly by opening up an allowed External IP (like a company VPN or even someone's home IP) or allow them to login to a VPN in your Azure network which can access the server. You may want to consider securing it with MFA.

[Andy @ Allerton!]

Where's the neckbeard emoji when I need it?

[Kashinoda]

Is your background T-SQL? I find mySql 'tricky' but only because I'm having to translate from T-SQL. Same for the few times I've used Oracle.
For sure there are some features not yet in mySQL but not ones I normally use. Heck even windowing functions send me running for google.

I work in BI and am pretty much tied to the Microsoft stack, T-SQL, DAX and Power Query. I use some MySQL (well, MariaDB) for hobbiest projects outside of work and I just find it a bit harder to work with. Like you say it's most likely because my brain is trained on the one thing.

I also think SQL Server Management Studio itself is great, I use DBeaver for MariaDB - any other suggestions welcome, ideally for Windows but I do use Linux too.

[Andy @ Allerton!]

I work in BI and am pretty much tied to the Microsoft stack, T-SQL, DAX and Power Query. I use some MySQL (well, MariaDB) for hobbiest projects outside of work and I just find it a bit harder to work with. Like you say it's most likely because my brain is trained on the one thing.

I also think SQL Server Management Studio itself is great, I use DBeaver for MariaDB - any other suggestions welcome, ideally for Windows but I do use Linux too.

You should try Neo4J

Talk about your brain having to mush its way around a new paradigm

You can try it on your local machine and there is a free version - headNoodles!

[Kashinoda]

You should try Neo4J

Talk about your brain having to mush its way around a new paradigm

You can try it on your local machine and there is a free version - headNoodles!

After watching several videos on Neo4J, I'm out

[Andy @ Allerton!]

After watching several videos on Neo4J, I'm out

If you love data, it's amazing mate

Not sure, but I think it's based on Personal Brain that was shipped with Encylopidia Brittanica years ago

I built some stuff with that, but had to do some work with <redacted> to sort out some <redacted> stuff and link <redacted> roles with <redacted> <redacted>



Your head has to change to use it. If you use SQL and understand Relational Databases then your headnoodle is mad.

But. Like the two vases making a face, this is amazing tech. It's incredible what a simple search can reveal.

Look up Neo4J and NoSQL

Mind. Blowing.

[Rhi]

One of my potential use-cases this year is to build a fraud detection tool with graph networks / neo4j. A lot of third party tools for financial fraud detection use this approach now, and they charge silly money for it.

[Andy @ Allerton!]

One of my potential use-cases this year is to build a fraud detection tool with graph networks / neo4j. A lot of third party tools for financial fraud detection use this approach now, and they charge silly money for it.

If you need a hand give me a shout mate.

I did take some notes, but it's mad.

Amazingly powerful though when you sort it out in your head. It's the opposite of a relational database. Nothing is about data. Everything is about relationships. There is no structure - structure makes no sense. It's a set of json strings and the queries are made via relationship.

[Graeme]

SQL isn't really my area of expertise so I wouldn't know what to expect salary wise, but where I work is taking on a SQL DB Administrator. The advert will say Preston but the post is within Design & Implementation where I work and I've been at home for the last 2 years and as it stands going to the office is optional...

https://jobs.lancashire.gov.uk/in/jobs/sql-database-administrator-13404

[PaulF]

You can change the CIDR Block to 0.0.0.0/0  - but why would you want to do that? Why would you want a public one that's not IP locked?

If you're calling it from 'somewhere' then you only want authorised users or apps accessing a database from a known IP source (Can be a Company VPN or an Azure VPN)

Not done that much with Azure to be honest. I'm mostly AWS and GCP.

People regularly scan and try and attack AWS/GCP/Azure at every single second. They'll be scanning for open ports on every range - thousands of them and if they found an open SQL server then they'd be running password crackers on it 24/7.

Even on our secured open systems it's always interesting to see the Imperva or Cloudwatch logs. The number of declined port scans is scary

Remember that you have private (internal) IPs and Public (External) IPs.

Apps and servers in your VPC will have access to the database indirectly usually.

So you'll have an Internet Access Point which will be pointed at your public subnet - this will have stuff that you want to access from the Web/Anywhere. These will have firewalls and stuff from the Cloud provider and then you can add other stuff.

These will be able to call the private subnet where your application servers reside. These won't be publically accessible.

These in turn can then call the persistance layer where your Database servers etc. are located.

So the environments are airgapped away from your data.

You may provide access for DBAs to access the Database server directly by opening up an allowed External IP (like a company VPN or even someone's home IP) or allow them to login to a VPN in your Azure network which can access the server. You may want to consider securing it with MFA.

I don't understand most of this. But kind of taken your advice. In the MS stack it's pretty simple to create Rest APIs to expose the data you need.
I guess the plus is people can hit the API as much as they want but they can't get directly to the db. Cheers.


--edit-- can I find time to play with Neo4J...

[ToneLa]

Lol I thought IT secrets on RAWK would be more here's how I made Windows 10 look like Ubuntu with an animated Klopp background

  that is a compliment

[Andy @ Allerton!]

Lol I thought IT secrets on RAWK would be more here's how I made Windows 10 look like Ubuntu with an animated Klopp background

  that is a compliment

You can make Windows 10 look like Ubuntu with an animated Klopp background?!

The advances of computer science thesedays!! :O

[Andy @ Allerton!]

I don't understand most of this. But kind of taken your advice. In the MS stack it's pretty simple to create Rest APIs to expose the data you need.
I guess the plus is people can hit the API as much as they want but they can't get directly to the db. Cheers.


--edit-- can I find time to play with Neo4J...

This diagram shows an example AWS Deployment.

It shows a web service name (Handled by Route53 which sorts out DNS/IPs) - notice that CloudFront is on there to - so you can distribute it and also help avoid DDOS attacks - this one will read a hosted webpage from an S3 Bucket (Which is like a folder with static data in it - like a website)

It shows a Web Layer, an App layer and although it's not marked, a persistance layer with a Master and Slave Database setup (This is quite usual and you'll have your web and app stuff across two regions (Which are automatically handled by a Load Balancer - of which there are a few types) - although it might be in one region and over two availability zones (Marked as AZ on the diagram)



It also shows autoscaling and this can be horizontal (More servers as load increases and less as it decreases) or vertical (bigger stuff)


If you want to know what those actual things are then go to the AWS help pages and search for the names as there is tons of info

The other stuff on there is to do with logging/monitoring/alerting/notifications etc. The Elasticache thing is a caching service (So that if the Database is chocka, it can cache it and allow it to get in a queue to be processed - quite often if you don't care about it actually getting written, you can 'fire and forget it' and a queue and then return and say 'that's done' while a bit later the queue is read and the data is written. If the data can't be writen, you don't lose it and it can end up being in a DLQ (Dead Letter queue) which can be automatically or manually handled))

But that's opening up a whole different subject, so ignore all that for now

[owens_2k]

Does anyone know a way of downloading multiple files from mediafire without having to download each one individually or purchasing a premium sub? Back in the day there would have been a chrome add-in which would have done it but I cant seem to find anything.

[owens_2k]

Sorted it with Jdownloader

[Andy @ Allerton!]

Sorted it with Jdownloader

You could share what you did on here

[owens_2k]

You could share what you did on here

Downloaded the .jar version of Jdownloader2 and magically when I opened it, all the files I wanted were listed and I just clicked download! It must have pulled the links from my browser somehow.

[Claire.]

Windows Remote Desktop question that I'm failing to find a reason for in the settings - I remote into my work laptop and when it's on battery power the little fucker keeps going to sleep and disconnecting the session, it's not when it's idling because I'll be actively using it and it'll only come back to life after I've pressed a key or whatever on the laptop itself. I've worked like this for years with my old dell and it never did this.

It doesn't do it when its connected to power but I don't want to fuck the battery by leaving it plugged in permanently. I've checked the power settings and I can't see anything that stands out as being weird, it's not idling so it shouldn't be turning itself off?! is there another menu somewhere I need to find or anyone got any ideas why it's doing it?

[PaulF]

Which one is sleeping? the one you are remoting into, or the one you are remoting from?
Not that I have an answer either way.
However remote desktop is designed for servers I guess, which are always on, so wouldn't trigger a 'host' machine to stay running.
I did read that you should leave a Mac plugged in to stop the battery from recycling to make it last longer. That sounded counter to what I know, but I'm not an expert on battery tech. Thought I'd add it here as a point of interest.]

I'd ask why the hell you are rdping into a laptop you can reach, but I know I'd have done the same, though I can't remember why.
The solution of course is to getting one of those nodding birds on a spring to keep tapping a key (C) homer simpson.

[Elmo!]

The difference between leaving your charger in all the time, and letting it discharge and recharging it is debatable. Both reduce the battery lifespan. I think the optimal thing to do if possible is set your laptop to only charge to ~50% and leaving charger in all the time. The most important thing for battery life is making sure your laptop doesn't overheat.

I'm not sure on the answer to your question sorry. RDP'ing from a personal computer to a work computer is a big security no-no in my place.

[PaulF]

I'm not sure on the answer to your question sorry. RDP'ing from a personal computer to a work computer is a big security no-no in my place.

Would doing that be unsafe if over VPN?

[Elmo!]

Would doing that be unsafe if over VPN?

Not as unsafe as doing it inside your office but you are introducing an entry point into your corporate network.

[Claire.]

The one I'm remoting into is going to sleep, my setup is weird I know, I'm front end so need _all_ the browsers and can't be looking at a tiny non-retina screen all day, always worked fine with the other laptop - compared the settings and nothing is different. Stupid thing.

[Graeme]

Is the remote PC connected to a domain?

[Andy @ Allerton!]

The one I'm remoting into is going to sleep, my setup is weird I know, I'm front end so need _all_ the browsers and can't be looking at a tiny non-retina screen all day, always worked fine with the other laptop - compared the settings and nothing is different. Stupid thing.

Give Royal TS a go. It's excellent and you can use it for all sorts of stuff

https://royalapps.com/ts/win/features

[Port_vale_lad]

Being a massive Stephen king fan, imagine my disappointment upon opening this thread.

[Chakan]

Being a massive Stephen king fan, imagine my disappointment upon opening this thread.

[ToneLa]

The difference between leaving your charger in all the time, and letting it discharge and recharging it is debatable. Both reduce the battery lifespan. I think the optimal thing to do if possible is set your laptop to only charge to ~50% and leaving charger in all the time. The most important thing for battery life is making sure your laptop doesn't overheat.

I'm not sure on the answer to your question sorry. RDP'ing from a personal computer to a work computer is a big security no-no in my place.

Yeah this is true for lithium, phones, tablets.

Keep it middle, charge it when you need. Exhausting the battery isn't good. Leaving it charging all the time is stupid too

[duvva 💅]

I’ve got an SD Card I’d forgotten about in an old camera. I’m sure we had more photos on it than those showing in the memory. Is there a good online tool to try and recover photos from an SD card anyone knows of?

[jonnypb]

I’ve got an SD Card I’d forgotten about in an old camera. I’m sure we had more photos on it than those showing in the memory. Is there a good online tool to try and recover photos from an SD card anyone knows of?

I've used piriform recuva with success in the past for things like this, was several years ago now though so not sure if it's still on the go or not.

[duvva 💅]

I've used piriform recuva with success in the past for things like this, was several years ago now though so not sure if it's still on the go or not.

Thanks for the suggestion, I’ll have a look at that tomorrow.

I downloaded one called Disk Drill this evening and it seemed to recover a few photos and videos, but you have to pay to have a pro edition to recover more than 500mg (recovery was over 4gb - which I was a bit surprised at as it only looked like it found an additional video and handful of photos).

Was the Recuva one free or cheaper at least, as this one wants £89! to get the pro edition so I can download most of what’s been recovered

[Wabaloolah]

[TepidT2O]

You have no idea how utterly shit the databases schools use are.
They are essentially a closed market and have barely changed in 20 years…

[rob1966]

I was in Germany setting up the system for our franchise, sat in an office with my boss when I hears "oh fuuuuucccckkk". 

What's up? He'd only gone and ran the following and wiped the whole table (the first semi colon dictates what got ran)

DELETE
FROM cust;
Where cust_code = AA1234;

Ah well back to the old system while we recover all the customer data

[jonnypb]

Thanks for the suggestion, I’ll have a look at that tomorrow.

I downloaded one called Disk Drill this evening and it seemed to recover a few photos and videos, but you have to pay to have a pro edition to recover more than 500mg (recovery was over 4gb - which I was a bit surprised at as it only looked like it found an additional video and handful of photos).

Was the Recuva one free or cheaper at least, as this one wants £89! to get the pro edition so I can download most of what’s been recovered

I don't think I paid for it, but possibly got it from a torrent site tho.  Just had a look and there's a free version and paid one for £20.  Got to be careful as the more you play about and access the card, the less chance you have of recovering data if blocks get overwritten etc.

[PaulF]

I was in Germany setting up the system for our franchise, sat in an office with my boss when I hears "oh fuuuuucccckkk". 

What's up? He'd only gone and ran the following and wiped the whole table (the first semi colon dictates what got ran)

DELETE
FROM cust;
Where cust_code = AA1234;

Ah well back to the old system while we recover all the customer data

Nice
And he'd not even surrounded the AA123 in quotes. Nor wrapped it in a transaction so he could rollback just in case.

[SalahsLeftFoot]

They are essentially a closed market and have barely changed in 20 years…

And that, in a nutshell, is one of the major problems with most of the public sector

[SalahsLeftFoot]

I was in Germany setting up the system for our franchise, sat in an office with my boss when I hears "oh fuuuuucccckkk". 

What's up? He'd only gone and ran the following and wiped the whole table (the first semi colon dictates what got ran)

DELETE
FROM cust;
Where cust_code = AA1234;

Ah well back to the old system while we recover all the customer data

Not as bad but ... at a previous job I was working on a project for GAME/Electronics Boutique's initial online presence. All the testing went and the product went live without a single issue - huge congratulations and slaps on the proverbial back all round. Fast forward a few months and the sales executive for their account gets a call from them saying they haven't had any online orders for ages. Long story short, turns out the Orders DB table went LIVE with a SMALLINT data type for the orderID column   

And later for the same client ... they wanted to have a treasure hunt feature on the site (where if you were ones of the first 100 people that day who spotted an icon on the site and you clicked on it then you got to win a new Gameboy - yes we are talking that long ago). Probably not a good idea to let the devs have UPDATE permissions on the winners DB table after the feature had gone live