Connecting to a standalone SQL Server on a Workgroup

Q: I cannot connect to our separate SQL Server 2005 (Developer Edition) using Windows Authentication. Is that because I am on a Workgroup instead of a Trusted Domain?

A: From a workgroup, the correct procedure is to use a SQL-Server login account (also known as SQL authentication) instead of a Windows account because by definition, workgroups are not trusted.

SQL Server authentication relies on the internal user list maintained by the SQL Server computer. This list does not include Windows NT users and is specific to the SQL Server computer. Users are created and configured using the SQL Server Management Studio (SSMS).

Set Mixed Authentication Mode:

To enable a login in SQL Server, open SSMS (from the Server) and make sure you are running in “Mixed” mode. Do this by right-clicking on the server node (top level) of the Object Explorer and selecting ‘Properties’. Then select the “Security” page. You should have the “SQL Server and Windows Authentication mode” selected. If you make a change, Right click on the server node and choose ‘Restart’ for the changes to take affect.

Enable SQL Server login:

Under the server node, expand ‘Security’ and ‘Logins’. Select an existing login (or right click on “Logins” to add a “New Login”). From the ‘Properties’ page, enter and confirm a password. (Save this password in a safe place). Select ‘User Mapping’ and check the box next to the AceMapper database. Check the “db_owner” database role below. Select ‘Status’ under ‘Select a page’ and make sure Logins are “Enabled”.

Test the connection using SSMS from another machine on the network connected to the Server. Select the Server name and select SQL Server Authentication from the drop-down which enables you to enter the user and password setup above.

If SSMS can connect, try connecting using AceMapper by unchecking “Use Integrated Security” and entering the user and password.