In SQL Server, there are 2 possible types of Authentication:
- Windows – Local Windows user accounts to access SQL Server
- SQL – Configured user accounts within SQL Server, such as the ‘SA’ account, which is the default admin account (similar to the ‘root’ account in MySQL)
SQL Server can be configured to allow only Windows Authentication or ‘Mixed-Mode’, which allows either a valid Windows or SQL user login to connect to SQL Server.
To identify what authentication modes are enabled in SQL Server:
Step 1) Open SQL Server Management Studio and connect to the SQL Server
Step 2) In the left-hand pane (object explorer) right click on the Server Host Name and select Properties
Step 3) In the resulting Dialog box, select the Security tab
Step 4) Under Server Authentication, the configured mode will be selected via the radio box
If it’s configured for Windows Authentication mode only, the SA user login (or any other SQL Account) will not be able to connect to SQL Server
To enable the sa account to be used:
a) select the ‘SQL Server and Windows Authentication mode’ and then Click OK
b) In the left-hand pane (object explorer), expand the Security folder, and locate the ‘sa’ user account within the User Logins folder
c) Right click on the sa user account and select properties
d) Select Status
e) Ensure the account is Enabled and then click OK
f) Restart the SQL Server service by right clicking on the Server Host Name (in the left hand object explorer pane) and select Restart
If Variphy cannot authenticate to MS SQL server:
Once you have confirmed you are using the correct logon and logon type, password, and case
Step 5) Make sure the port is set to 1433 (changing the SQL server type drop box will not change the default MySQL port number of 3306, must be changed manually)
Step 6) If authentication issues persist, verify the port is listening with the netstat -ano command in a CMD browser. If port is listening but you still can’t connect, check the Server port properties in SQL Server Configuration Manager (SSCM) that 1433 is being used.
a) Under SQL Native Client Configuration, ensure that TCP/IP is enabled under “Client Protocols” and that the default port is set to 1433 in the TCP/IP properties.
b) Under SQL Server Network Configuration, ensure that TCP/IP is enabled under the “Protocols for (SQLserverVersion)“. In the TCP/IP properties under the IP addresses Tab Scroll to the bottom secition IPAll, and check if a dynamic port is in use. If it is, change the TCP Dynamic Ports field to 0and put in 1433 for the TCP Port. Apply and Save the change, then re-open the tab. You should now see the TCP port, and a new dynamic port above it (usually the next number up from the previous dynamic port). SQL server should now use the designated TCP port by default and disregard the dynamic port.
**Note – the dynamic port is usually only configured in express versions of SQL server. The standard Professional/Enterprise version of MS SQL should not use dynamic ports by default.
