How to install and sign SqlPackage on macOS

A long time ago in a galaxy far, far away…

This post is over 12 months old, that's a long time in tech! Please be mindful of that when reading this post, young Padawan, as it could be outdated. I try to keep things up to date as much as possible. If you think something needs updating, please let me know in the comments.

2024 update

There is a simpler way of installing SqlPackage on macOS using dotnet. I have written a new post on this which you can read here. The instructions below are probably still valid, but the new post is far simpler to complete.

Introduction

I predominantly use a Mac and so that makes working with Microsoft data products a bit interesting at times. Fortunately more and more tools are natively available on macOS. SqlPackage runs on macOS but needs a bit of configuration to get it to work. I didn’t like Microsoft’s instructions, I have tweaked them slightly to get them to work more effectively. Microsoft’s instructions can be found in MS learn. Read on to see what I have changed, and why.

Download

First download the latest SqlPackage using this evergreen link. Rather that altering the paths in the code, I rename the downloaded file to sqlpackage.zip.

Install

I made some subtle adjustments to the code suggested by Microsoft. If you want to understand what changes I have made first, read through then come back and run the commands. Microsoft’s guide suggests you run this code;

mkdir sqlpackage
unzip ~/Downloads/sqlpackage-osx-<version string>.zip -d ~/sqlpackage
chmod +x ~/sqlpackage/sqlpackage
echo 'export PATH="$PATH:~/sqlpackage"' >> ~/.bash_profile
source ~/.bash_profile
sqlpackage

I have taken a slightly different approach. First I need to unzip and prepare the directory;

mkdir ~/.sqlpackage
unzip ~/Downloads/sqlpackage.zip -d ~/.sqlpackage
xattr -c -r ~/.sqlpackage
chmod +x ~/.sqlpackage/sqlpackage

I have omitted two commands because they are specifically for bash and on more recent Macs, zsh is the default shell. I have also amended the zip file reference. It’s just easier to do that than mess with the code. I have also changed the directory to ~/.sqlpackage. Directories that start with a full stop are hidden in macOS. I have no need to see the directory and I hate clutter.

The main change is adding the line xattr -c -r ~/.sqlpackage. This command strips the extended attributes from the SqlPackage directory and contents. If you have ever installed an app from the internet on your Mac and got the message “macOS wouldn’t run this app because it’s not approved” when you tried to open it, you would have then had to go to settings and manually approve running the app.

That’s because macOS runs something called Gatekeeper which provides a level of protection over content downloaded randomly from the internet. It’s fine for downloaded apps, but downloading commands like SqlPackage? Well that ends up being super messy because you end up with having to approve EVERY SINGLE BINARY. That’s just not fun…

Microsoft references this on the instruction page…

Security settings may require modification to run SqlPackage on macOS. Use the following commands to interact with Gatekeeper from the command line.

…but this is a bit negligent on their part in my opinion, since it suggests that you disable gatekeeper whilst running SqlPackage and re-enable it afterwards. It’s just asking for someone who doesn’t know any better to just leave it disabled. The xattr command removes the controls for SqlPackage permanently and is a much cleaner solution.

Set $PATH

Next I need to update $PATH to include the path to sqlpackage however, this will be different depending on which shell you use. Also, it only needs to be run if I don’t already have that path listed, running it again will duplicate the lines which is just a bit messy.

I want to check whether the path to sqlpackage already exists in the respective profile file. To do this I can use the cat command, which will read the contents of a file directly to the terminal window. First I will check the relevant file for zsh which is ~/.zprofile;

justin@Justin-MacStudioM2 site-blog % cat ~/.zprofile
eval "$(/opt/homebrew/bin/brew shellenv)"

# Setting PATH for Python 3.11
# The original version is saved in .zprofile.pysave
PATH="/Library/Frameworks/Python.framework/Versions/3.11/bin:${PATH}"
PATH=$PATH:~/.sqlpackage
justin@Justin-MacStudioM2 site-blog % 

In the example above lines 2 to 7 are the output from my .zprofile file. I can see that I already have a reference to sqlpackage in this file, so I don’t need to update it. Calling sqlpackage should already work. The relevant file for bash is ~/.bash_profile;

Justin-MacStudioM2:site-blog justin$ cat ~/.bash_profile
# >>> conda initialize >>>
# !! Contents within this block are managed by 'conda init' !!
__conda_setup="$('/Users/justin/opt/anaconda3/bin/conda' 'shell.bash' 'hook' 2> /dev/null)"
if [ $? -eq 0 ]; then
    eval "$__conda_setup"
else
    if [ -f "/Users/justin/opt/anaconda3/etc/profile.d/conda.sh" ]; then
        . "/Users/justin/opt/anaconda3/etc/profile.d/conda.sh"
    else
        export PATH="/Users/justin/opt/anaconda3/bin:$PATH"
    fi
fi
unset __conda_setup
# <<< conda initialize <<<

export PATH="$PATH:/usr/local/bin"
Justin-MacStudioM2:site-blog justin$ 

Here lines 2 to 17 are the output from my .bash_profile file. There is no reference to sqlpackage so I do need to update this file. So I only need to update bash. Depending on your output from cat, you may need to update both, or one, or none of them. Here are the two commands, decide which needs to be run and then run them from the respective session;

# zsh
echo 'PATH=$PATH:~/.sqlpackage' >> ~/.zprofile

# bash
echo 'export PATH="$PATH:~/.sqlpackage"' >> ~/.bash_profile

Source file

The other command I took out was the source command. This loads your profile into the current session and essentially updates $PATH with your new update however, you should be in the corresponding shell session to get it to work.

# zsh
source ~/.zprofile

# bash
source ~/.bash_profile

Test sqlpackage

Once you have run the above commands, you should be able to call SqlPackage and prove that it is working just by typing sqlpackage into your terminal session.

sqlpackage

SqlPackage: Command-line tool for creating and deploying SQL Server databases and DACPAC packages. (162.0.52.1)
Copyright (c) 2023 Microsoft Corporation.  All rights reserved.

Help for dynamic property usage.
/@<file>:<string>
     Read response file for more options.

/help:[True|False]
      (short form /?)

/version:[True|False]
     Display the tool version in use.

Mission accomplished!

Thanks to @mikeymikey @hachyderm.io

So a nod to @[email protected] over on Mastodon who advised me on how to address the daft gatekeeper issues when I vented on there. You can read that discussion on Mastodon.