At a project, we asked if we could get a SQL Server login/user so we could make a connection from an Apache Airflow pipeline to the SQL Server instance. The answer was: “No, because then we would need to restart the server”. This sounded a bit strange, because I’ve never had to restart the SQL Server instance to add a new user.
I double checked on Google and I couldn’t find any info on why you should need to reboot the server. I asked on Twitter, and even no-one of the #SQLFamily could find a reason. I was starting to question the sanity of the admins at the project. However, after a quick discussion we finally discovered the reason why:
SQL Server authentication was not enabled on the instance.
The default authentication mode when installing SQL Server is Windows Authentication only:
If you want SQL Server authentication, you need to explicitly enable it during set-up (so no next-next-finish). In this case, it wasn’t the case. You can switch to mixed mode later on, but you might’ve guessed it: this requires a restart of the SQL Server instance.
All this learned me one important lesson: when someone gives you a surprising answer, don’t make assumptions. They probably have a good reason.