There may come a day where you need to verify you have access to connect to a SQL Server instance. Perhaps you need to verify this access for a hundred or maybe even a thousand servers. Doing this by hand is not great and you’re going to want to capture what you did and did not have access to. This PowerShell script will help you accomplish that.
Continue reading “Verify access to many SQL Server instances”Category: Administration
Get notified when a user with administrative privileges logs into your SQL Server
I was helping someone set up some monitoring in their database and they were asking about being notified when someone with administrative privileges logs into SQL Server. While many of you know that I try to use the right tool for the right job, some may cringe when I say triggers can help out in this scenario.
Continue reading “Get notified when a user with administrative privileges logs into your SQL Server”
Permission was denied on the object ‘master_properties’, database ‘SSISDB’, schema ‘internal’.
I ran into an interesting issue where all DDL and DML statements were being denied on my SSISDB, but specifically on my Internal schema. Here’s a few example error messages I saw:
Description: The SELECT permission was denied on the object ‘master_properties’, database ‘SSISDB’, schema ‘internal’. The UPDATE permission was denied on the object ‘operations’, database ‘SSISDB’, schema ‘internal’. The EXECUTE permission was denied on the object ‘insert_operation’, database ‘SSISDB’, schema ‘internal’.
The SELECT permission was denied on the object ‘current_user_readable_projects’
I’m not sure when or how this issue came to be, but I do know how I solved it!
Resolution:
Make sure dbo owns schema Catalog and also owns schema Internal.
Both schemas were owned by another user who was not ‘dbo’.
What else did I try? Just about everything.
I changed from local system account running the SQL Agent to a known domain sys admin user. I don’t recall the exact error, but it gave an error stating the user needed to be a member of the sysadmin group or I needed a proxy account.
When I added the user to Sysadmin or created a proxy account, the original errors returned.
I double checked the deny permissions, those were not set.
At one point I started to receive this error:
Failed to execute IS server package because of error 0x80131904.
Description: The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘SSISDB’. You should correct this situation by resetting the owner of database ‘SSISDB’ using the ALTER AUTHORIZATION statement.
I set Master and SSISDB to have the same owner here and still saw the same error afterward.
Here’s the URL that helped me the most to resolve the problem:
I have added in a form for feedback, it would be very helpful if you would take a few minutes to fill this out. My goal is to adjust the content to best help others resolve their problems.
Alert when SQL Server jobs fail
I would always recommend an enterprise monitoring solution for your important SQL Servers. There may be some that are less important though. You can always configure alerts at an individual job level, you can create triggers or extended events to notify you, or even use base alerts in SQL Server. If you need a quick and dirty solution to throw into place temporarily though, I like this option.
Continue reading “Alert when SQL Server jobs fail”SQL Server Database in Suspect mode
I’m going to preface by saying, I ran into a specific scenario and I strongly suggest you see if this scenario is what’s currently affecting you before you try these troubleshooting steps.
Continue reading “SQL Server Database in Suspect mode”Ola Hallengren not truncating T-Logs after backup. Prefer Secondary.
Scenario, you have an availability group with active / passive (read only) set up and backups are set to be preferred on secondary. You install Ola on nodes A (RW) and B (R). A few days later, your T-Logs fill up and things are broken, what’s going on?
To cut to the chase, my problem was that I did not have T-Log backups installed on A and B, I only had them installed on A while I had the Full installed on A and B. So this means each weekend we would still get a full backup. When I checked the listener or node A/B, the full backups looked great. I didn’t realize immediately to check the T-Log job on the B node (secondary). Putting the T-Log backup process on A / B and scheduling them the same fixed it for me.
When I kicked the process off via the listener, it was running this command:
SELECT d.database_name, sys.fn_hadr_backup_is_preferred_replica (d.database_name) AS IsPreferredBackupReplicaNow FROM sys.availability_databases_cluster d
It basically returns a yes or no saying; Hey guys, I’m the node you want to take the backup on. If the job runs and sees a no, it ignores the database to backup on that node. Meanwhile the other node runs this same command and sees yes instead, it decides to backup the database. (The command is built into Ola scripts.)
A gotcha I ran into was running the job manually from the listener did not help my situation with T-Logs truncating because it was running the backup on the wrong node. So I did a lot of fruitless testing since I was starting the job from the listener. Hopping to the secondary and running the job there gave us T-logs and truncated them effectively.
I did run into a chain problem initially once I discovered the problem and solution. (Where I needed to take a new Full backup to base my Transaction Logs off of.) To determine exactly what the problem was, I ran this command:
SELECT name, log_reuse_wait_desc, recovery_model_desc FROM sys.databases
Here’s an explanation of the log_reuse_wait_desc column:
Explanation of log_reuse_wait_desc
I would recommend to follow Ola’s suggestions on setup and to read all documentation carefully when doing this for the first time as following his process should keep you problem free.
Brent Ozar also has some good tips and information for setting up Ola’s scripts on Availability Groups.
Connecting PowerBI to RaspberryPi (MariaDB Engine)
It’s been a little while since I had PowerBI working against a remote repository on my Raspberry Pi and I had some troubles recalling how to configure it. As a result, it was an excellent experience troubleshooting it. This also led to me realizing, there’s no guide to configuring PowerBI to MySQL(MariaDB) on a Raspberry Pi. There are a lot of similar posts, but the problem with most of scenarios I found online is that the MySQL DB was most frequently running locally with PowerBI, whereas in my circumstance it was remote.
The primary issue I ran into was seeing “Details: MySQL: Unable to connect to any of the specified MySQL hosts.” I was able to connect to my instance with phpmyadmin and also with MySQL workbench, so I felt I was able to connect with PowerBI here too. I installed MariaDB Workbench just to test and see if I could at least access my instance with that tool. It turned out I ran into a similar issue. The problem is twofold.
1. The bind-address for my MySQL instance was set, not allowing remote IP calls.
2. I needed a user with a correct domain to access the box with a SQL login.
To resolve the first issue, I had to find my config file. MariaDB is set up to import four different config files, the one I edited was /etc/mysql/mariadb.conf.d/50-server.cnf. A quick # in front of #bind-address=127.0.0.1 and a sudo /etc/init.d/mysql restart got the IP fixed up quick.
To resolve the second issue, I created a user with extra permissions. IMPORTANT NOTE: This is NOT best practice. My Pi is on a private network and does not host any secure data. So I’d recommend taking this as an example and working with it to fit your security needs.
I create a user with admin privileges that has access open to any IP. So I’ll be able to remote into the Pi from any device on that private network with that login now.
CREATE USER 'someuseraccount'@'%' IDENTIFIED BY 'some_pass'; GRANT ALL PRIVILEGES ON *.* TO 'someuseraccount'@'%' WITH GRANT OPTION;
In PowerBI, I went to Get Data -> MySQL DB -> put in my server as 192.168.1.155 -> the name of my Database -> and set the login for Database and used the user and password I had just created in the last step.
Now I’ve seen where other users had issues and it was due to a driver issue, so make sure you have your driver updated for MySQL.
Here is where I obtained the idea for the bind in step 1.
Here is where I obtained the idea for the user in step 2.
MySQL (Really, MariaDB) on the Raspberry Pi!
I’ve had a few people ask me lately about how I got MySQL running on my Raspberry Pi, so here’s my guide on how to go from almost 0 to 100. This guide assumes you understand the basics of Linux and Raspberry Pi’s. You’ve probably done a few different tutorials and you have tinkered with it before.
1. Download Raspbian.
2. Wipe the sd card and format it. (SDFormatter is a free tool you can use.)
3. Unzip the file.
4. Write the image to disk. (Win32 Disk Imager is a free tool you can use.)
5. Copy a wpa_supplicant.conf file and ssh.txt over to the boot partition of the sd card. You’ll need to edit the conf to contain your current wifi information, the below is a good example of a file you can modify and use. The ssh.text file is just a blank file with the name ssh so the Pi knows to enable SSH on boot.
country=US
ctrl_interface=DIR=/var/run/wpa_supplicant GROUP=netdev
update_config=1
network={
ssid="YOUR WIFI IS GONNA GO HERE"
scan_ssid=1
psk="YOUR PASSWORD IS GOING HERE AND IF YOU DON'T USE ONE THEN SHAME ON YOU"
key_mgmt=WPA-PSK
}
6. SSH into the PI. (Putty is a free tool you can use.)
7. Run this on your Pi to get RDP working. It’s just a nice to have but you can skip this if you won’t use it, we won’t be using it in the tutorial.
#remove the following packages : xrdp, vnc4server, tightvncserver sudo apt-get remove xrdp vnc4server tightvncserver #install tightvncserver followed by xrdp sudo apt-get install tightvncserver sudo apt-get install xrdp
8. Now update the Pi so it’s current.
#update your system's package list by entering the following command: sudo apt-get update #Next, upgrade all your installed packages to their latest versions with the command: sudo apt-get dist-upgrade
8. Get My-SQL.
sudo apt-get install mysql-server
9. Configure security for MySQL, this is going to ask you about creating a root user. Make sure you give it a password, MyPHPAdmin will NOT work if you don’t make your root user with a password. I made this mistake the first time and then installed phpmyadmin, this was where the root password got stored and I had to re-run the configuration for phpmyadmin by following the steps posted below: (Credit to Amigo Chan on askUbuntu) (For anyone googling this error, I saw errors such as “not enough privilege to view users. phpmyadmin” and “access denied for user ‘root’@’localhost’ TO DATABASE phpmyadmin”.)
sudo mysql_secure_installation
10. Install bindings for python for later use.
sudo apt-get install python-mysqldb
11. Create a mysql user.
sudo mysql -u root -p GRANT ALL PRIVILEGES ON mydb.* TO 'USERNAME'@'localhost' IDENTIFIED BY 'PASSWORD GOES HERE'; quit
12. Get apache2 up and running.
sudo apt-get install apache2 php5 libapache2-mod-php5
13. Get phpmyadmin up and running.
sudo apt-get install phpmyadmin
14. Configure apache2.
sudo nano /etc/apache2/apache2.conf
15. Restart apache2.
sudo /etc/init.d/apache2 restart
16. Now you need to go to a webbrowser and put in the IP of the Pi and /phpmyadmin at the end. Example(192.168.1.155/phpmyadmin)
And Voila! You should be in!
Additional resources used:
https://pimylifeup.com/raspberry-pi-mysql-phpmyadmin/