Changing SQL Server to Mixed Mode

Follow the steps below to change an SQL server to mixed mode if this is required for an install running the CyTrack database. Mixed mode will allow users to access the CyTrack Database using the CyTrackuser and password via odbc.

1. Change the SQL server to use mixed mode authentication. An SQL express instance is installed as part of a standard CyTrack installation which would default to Windows Authentication mode only. The chnage is detailed on the following Microsoft learn page: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode?view=sql-server-ver16 .

2. Do not enable sa login, since that is a security risk. We will only create and enable the CyTrackUser.

3. Connect to the SQL server via the SQL Management Studio via a windows user with sufficient privileges.. The user used during the initiall installation of CyTrack producst will suffice.

4. Run the below code at this prompt (This will modify the registry, so do take care.): {{USE [master] }}

   {{GO }}

   {{EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1 }

   GO

5. Restart the database service for above change to be picked up. Note all CyTrack services using the database will need to be stopped to restart the database service. Right click on the server in Object Explorer and select restart.

   Open

   

6. Connect to the server as the windows user. Create a new query. Ensure that the selected database is the CyTrack database (run ‘select top 10 * from tblLog’for example). Then run: CREATE LOGIN CyTrackUser
     WITH PASSWORD='<usestrongpassword>' , (contact CyTrack support for password to be used)
     DEFAULT_DATABASE=CyTrack,
     DEFAULT_LANGUAGE=english,
     CHECK_POLICY=OFF

7. Next run: CREATE USER CyTrackuserFOR LOGIN CyTrackuser

8. Finally, run: CREATE ROLE db_executor
   GRANT EXECUTE TO db_executor

   EXEC sp_addrolemember N'db_datareader', 'CyTrackuser'

   EXEC sp_addrolemember N'db_datawriter', 'CyTrackuser'

   EXEC sp_addrolemember N'db_ddladmin', 'CyTrackuser'

   EXEC sp_addrolemember N'db_executor', 'CyTrackuser'

9. Open SQL Server Configuration Manager and ensure TCP/IP is enabled under Protocols for MSSQLSERVER.

   Open

   

10. Add an inbound rule in the firewall for port 1433.

End