How to enable sa login when doing an unattended install of SQL Server 2014 Express?

Question

I have an installer that's running the SQL Server 2014 Express installer in unattended mode.

Basically, it's creating a command-line and running the setup.

My problem is that I need to be able to connect to the installed instance as admin using SQL Server authentication.

The command-line already contains /SECURITY MODE=SQL. I can create a SQL login and login successfully, so that part of the problem works fine.

My problem is that while I can see sa in sys.server_principals, it's flagged as is_disabled, and I can't login using it.

Is there a way, when running the SQL Server 2014 install unattended, to pass command line arguments that will have it enable sa so I can successfully login using it?

Or some other login, if that's easier.

What I need is a sql_login that I can use to connect to the database as an db administrator without regard for the permissions of the logged-in windows user, after having run the installer in unattended mode.

The full commandline args:

/QS /IACCEPTSQLSERVERLICENSETERMS /ACTION=Install /FEATURES=SQL 
    /INSTANCENAME=SQLEXPRESS /SAPWD="SQLSVCPASSWORD"

If I login to Windows using an admin account, I can connect to the database using Windows authentication. I can then create a normal SQL Server login. With that, I can then login using SQL Server authentication and that account.

So I'm certain the DB is in mixed mode. And this:

Exec xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode'

returns '2'.

My problem is that I need the users to be able to run the software without being a windows admin. And part of what the software needs to be able to do is to drop and create databases, within the instance.

So I need SQL admin permissions, without depending upon the windows user having elevated permissions.

===

The setup tool I'm using is configured using XML files that contain, in them, LUA scripts that build and run the Windows Installer package command lines. Between the nested languages and various levels of escaping, I'd not noticed that the "/SECURITYMODE=SQL" argument was commented out, and not included in the command line.

With it included in the command line, the "sa" user is enabled.

TL;RD If you want the "sa" user enabled, after an install, include "/SECURITYMODE=SQL" on the command line.

Answer 1

You need to specify /SAPWD as well when using /SECURITYMODE=SQL. I am not sure, but if you do not specify the password, it will be disabled by default.

Have a look at this article https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-2017 for more information.