SQL Server Authentication for CRM Software

SQL Server - Create Logins and Users

Download Contact Management Software     Reached the Final for a CRM Award in the Software Satisfaction Awards 2011 Reached the Final for a CRM Award in the Software Satisfaction Awards 2012

CRM Testimonials

SQL Server Authentication for CRM Software

When setting up Microsoft SQL Server on a server for multiple user access for your CRM Software, you have to create SQL Logins to provide Authentication to connect to SQL Server and you have to create SQL Users to provide permissions to connect to the specified database. Until this is set up, your users will not be able to access the database.

Windows Authentication or SQL Authentication

You can use Windows Authentication or SQL Authentication to access the Microsoft SQL Server database. Microsoft SQL Server can be configured to allow Windows Authentication only or it can be configured to allow "mixed mode" which means that Windows Authentication and SQL Authentication are both allowed. SQL Authentication can be less secure as it uses usernames and passwords in the connection string.

If your users are on a domain, Windows Authentication would be the normal authentication mode to use. SQL Authentication is normally used if users outside of your domain require access to SQL Server or if your users are not on a domain.

SQL Server Authentication Mode

Windows Authentication

With Windows Authentication, the clients use a trusted connection to connect to the Server. If you run SQL Server Management Studio and go to Security->Logins you will see the SQL Logins that already exist.

SQL Server Logins

You may see a built-in Login called BUILTIN\Users which gives all domain users Public access to SQL Server by default which may be used though this is often removed to enhance security.

If you wish to use the BUILTIN\Users Login, you can just double-click on the BUILTIN\Users Login, select the User Mapping page, tick the Customer checkbox to give access to the Customer database and tick the db_datareader and db_datawriter checkboxes.

SQL Server Builtin Users

In countries that use British date format dd/mm/yyyy, you also need to change the Default Language at the bottom of the General page to British English. The default setting of English uses USA date format mm/dd/yyyy.

With Windows Authentication, the connection string specifies the server and database and specifies that a trusted connection is being used as in this example.

   Provider=SQLNCLI;Server=Server1\SQLEXPRESS;Database=customer;Trusted_Connection=yes;

The above example connects to SQL Server Express on Server1. If you are using the full paid version of SQL Server then you could use a connection string like this to point to the default SQL Server instance on Server1.

   Provider=SQLNCLI;Server=Server1;Database=customer;Trusted_Connection=yes;

SQL Authentication

With SQL Authentication (which will only work if SQL Server is configured in "mixed mode" to allow SQL Authentication) a SQL User is created eg crmuser and a password is specified. That SQL user is then given access to the relevant database.

With SQL Authentication, in the connection string you need to specify the SQL username and the password as in this example. Because the username and password are stored in the connection string, this is less secure than Windows Authentication. Please remember to use whatever password you enter into SQL Server in your connection string. This is just an example.

   Provider=SQLNCLI;Server=Server1\SQLEXPRESS;Database=customer;user id=crmuser;password=pa55w0rd;

The above example connects to SQL Server Express on Server1. If you are using the full paid version of SQL Server then you could use a connection string like this to point to the default SQL Server instance on Server1.

   Provider=SQLNCLI;Server=Server1;Database=customer;user id=crmuser;password=pa55w0rd;

Creating SQL Logins

(If you are using the BUILTIN\Users Login, you can ignore this section).

SQL Logins are created by going to the Security section in Microsoft SQL Server Management Studio and right-clicking on Logins and selecting New Login. Here you specify whether Windows Authentication or SQL Server Authentication is to be used for this Login.

For Windows Authentication you just need to specify the domain user, the default database ie Customer (for Amphis Customer) and the default language. For SQL Server Authentication you need to specify a name for the new SQL Login eg crmuser and specify a password.

SQL Server Create Login

Be careful that with SQL Authentication, if you leave Enforce password expiration and User must change password at next login ticked, the password will need to be changed at some point and the client PCs will not be able to connect to the database until the password is changed and the connection strings are all updated with the new password. In Amphis Customer, the connection string is stored in the config.mdb database file in the default data folder.

Creating Database Users

(If you are using the BUILTIN\Users Login, you can ignore this section).

Once you have created the SQL Logins you need to go to the Security section in Microsoft SQL Server Management Studio for the particular database you want to give access to eg Customer for Amphis Customer.

Right click on Users and select New User. Select the appropriate User type. If you are using Windows Authentication, select Windows User. If you are using SQL Authentication, select SQL user with login.

For a Windows User you need to specify the Login name including the domain eg Work\Fred and also specify the user name which doesn't need the domain eg fred.

For a SQL user, you need to specify the Login name as the SQL Login you created in the Security section eg crmuser. We suggest using the same name for the user name.

Database Permissions

(If you are using the BUILTIN\Users Login, you can ignore this section).

Select the Membership page and tick the db_datareader and db_datawriter roles to give the user read and write access to the Customer database.

SQL Server Database Permissions

IT Support Needed

Please note that setting up and maintaining Microsoft SQL Server is an advanced topic and we would suggest consulting an IT Professional to help you with this. The IT Professional needs to have a full understanding of Microsoft SQL Server including SQL Logins, SQL Users, networking and security, and SQL Server backups.

Amphis Software Support are happy to provide help with setting up Amphis Customer to use Microsoft SQL Server but setting up Microsoft software such as Microsoft Windows, Microsoft Exchange, Microsoft Outlook or Microsoft SQL Server is beyond the support that Amphis Software Free Support can provide (except for online support such as this page). We support software we have developed but we don't support software developed by Microsoft.

Microsoft SQL Server Language Setting

Please note that if you live in a country that uses British date formats eg United Kingdom or Australia, you will need to change the Default Language for the Logins to British English.

SQL Server British English Dates

To check this, go to Security->Logins and select the Login you are using. Then check the properties to see if the Default Language is British English.

SQL Server British English

If your dates are in British format dd/mm/yy but the SQL Server Login you are using is configured to be the default of English then your SQL queries will fail because the dates will be invalid as it will treat them as mm/dd/yy.

If you haven't set up any specific Logins you are probably automatically using the BUILTIN\Users Login so that would be the Login to change.

If you are not sure which Login to change to British English we suggest you change them all.