Is your feature request related to a problem? Please describe.
The Foundation setup.cmd script creates SQL Server logins for database access, but it doesn't verify that SQL Server Authentication is enabled. When SQL Server is configured for "Windows Authentication mode" only (the default), the setup appears to succeed but the application fails at runtime with connection errors because the SQL logins cannot authenticate.
Describe the solution you'd like
Add a check immediately after the user enters their SQL Server name to verify that SQL Server Authentication (Mixed Mode) is enabled. If it's not enabled, display a clear error message with instructions and exit before attempting database setup.
The check should be inserted after the :main label (around line 40 in setup.cmd), right after the SQL server parameters are set:
:main
if "%SQLSERVER%"=="" (set SQLSERVER=.)
if "%ADDITIONAL_SQLCMD%"=="" (set ADDITIONAL_SQLCMD=-E)
echo ## Checking SQL Server authentication mode ##
for /f "tokens=*" %%a in ('sqlcmd -S %SQLSERVER% %ADDITIONAL_SQLCMD% -C -Q "SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows' ELSE 'Mixed' END" -h -1') do set AUTH_MODE=%%a
if "%AUTH_MODE%"=="Windows" (
echo.
echo ERROR: SQL Server is configured for Windows Authentication only.
echo The Foundation setup requires SQL Server Authentication (Mixed Mode) to be enabled.
echo.
echo To enable Mixed Mode authentication:
echo 1. Open SQL Server Management Studio
echo 2. Right-click the server instance '%SQLSERVER%' and select Properties
echo 3. Go to Security page
echo 4. Select "SQL Server and Windows Authentication mode"
echo 5. Click OK and restart the SQL Server service
echo.
pause
exit /b 1
)
echo SQL Server authentication mode: %AUTH_MODE% - OK
echo.
cls
echo Your application name is: %APPNAME%
...
This way the user gets immediate feedback before the lengthy build and database setup process begins.
Describe alternatives you've considered
Use Windows Authentication instead of SQL logins (would require significant changes to connection string handling)
Document the requirement in README (less user-friendly, easy to miss)
Attempt the setup and provide a better error message when connection fails (reactive rather than proactive)
Additional context
This issue commonly affects fresh SQL Server installations, as the default installation mode is "Windows Authentication mode" only. Users may not realize SQL Authentication is disabled until the application fails to start with cryptic connection errors like:
System.Data.DataException: A transient error occured in the database: 'A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)'
The pre-flight check would catch this configuration issue immediately and provide actionable guidance, saving significant troubleshooting time.
Is your feature request related to a problem? Please describe.
The Foundation setup.cmd script creates SQL Server logins for database access, but it doesn't verify that SQL Server Authentication is enabled. When SQL Server is configured for "Windows Authentication mode" only (the default), the setup appears to succeed but the application fails at runtime with connection errors because the SQL logins cannot authenticate.
Describe the solution you'd like
Add a check immediately after the user enters their SQL Server name to verify that SQL Server Authentication (Mixed Mode) is enabled. If it's not enabled, display a clear error message with instructions and exit before attempting database setup.
The check should be inserted after the :main label (around line 40 in setup.cmd), right after the SQL server parameters are set:
:main
if "%SQLSERVER%"=="" (set SQLSERVER=.)
if "%ADDITIONAL_SQLCMD%"=="" (set ADDITIONAL_SQLCMD=-E)
echo ## Checking SQL Server authentication mode ##
for /f "tokens=*" %%a in ('sqlcmd -S %SQLSERVER% %ADDITIONAL_SQLCMD% -C -Q "SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows' ELSE 'Mixed' END" -h -1') do set AUTH_MODE=%%a
if "%AUTH_MODE%"=="Windows" (
echo.
echo ERROR: SQL Server is configured for Windows Authentication only.
echo The Foundation setup requires SQL Server Authentication (Mixed Mode) to be enabled.
echo.
echo To enable Mixed Mode authentication:
echo 1. Open SQL Server Management Studio
echo 2. Right-click the server instance '%SQLSERVER%' and select Properties
echo 3. Go to Security page
echo 4. Select "SQL Server and Windows Authentication mode"
echo 5. Click OK and restart the SQL Server service
echo.
pause
exit /b 1
)
echo SQL Server authentication mode: %AUTH_MODE% - OK
echo.
cls
echo Your application name is: %APPNAME%
...
This way the user gets immediate feedback before the lengthy build and database setup process begins.
Describe alternatives you've considered
Use Windows Authentication instead of SQL logins (would require significant changes to connection string handling)
Document the requirement in README (less user-friendly, easy to miss)
Attempt the setup and provide a better error message when connection fails (reactive rather than proactive)
Additional context
This issue commonly affects fresh SQL Server installations, as the default installation mode is "Windows Authentication mode" only. Users may not realize SQL Authentication is disabled until the application fails to start with cryptic connection errors like:
System.Data.DataException: A transient error occured in the database: 'A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)'
The pre-flight check would catch this configuration issue immediately and provide actionable guidance, saving significant troubleshooting time.