Configuring Microsoft SQL Server for visitor id

This article describes how to configure Microsoft SQL Server for use with visitor id. Please use these instructions when installing Microsoft SQL Server manually.

Step 1: Download the pre-configured SQL scripts

The SQL scripts that are used in the below instructions can be downloaded from this link: https://www.visitorid.com.au/downloads/SQL/visitoridSQLScripts.zip

Unzip this zip file into a temporary folder on the SQL Server PC.

Step 2: Apply required changes to the SQL Server Instance

1. Open the ‘Step 1 – Add Linked Server.sql’ file in Microsoft SQL Server Management Studio.
2. Connect to the visitor id SQL Server Instance. You may need to connect twice:
   
3. In the SQL script, change the text ‘\VISITORID’ to the name of the SQL Server Instance that the visitor id database resides on. For example: ‘MSEDGEWIN10\VISITORID’:
   
4. Make sure the ‘master’ database is selected and the click the ‘Execute’ button:
   
5. You will either get a success message or a message stating “The server ‘‘ already exists.”. Both of these messages are ok. Press the ‘x’ button to close the script window and continue to the next step. You do not need to save the SQL script when closing. If you receive an error message that is different, please take a screen shot and send an email to support@visitorid.com.au:
   
6. Open the ‘Step 2 – DATA ACCESS TRUE.sql’ file in Microsoft SQL Server Management Studio. You may be asked to connect to the SQL Server Instance again.
7. In the SQL script, change the text ‘\VISITORID’ to the name of the SQL Server Instance that the visitor id database resides on. For example: ‘MSEDGEWIN10\VISITORID’:
   
8. Make sure the ‘master’ database is selected and the click the ‘Execute’ button:
   
9. You should see a success message. Press the ‘x’ button to close the script window and continue to the next step. You do not need to save the SQL script when closing. If you receive an error message then please take a screen shot and send an email to support@visitorid.com.au:
   

Step 3: Create a new SQL Server Login and Change the visitor id Database Owner

It is highly recommended to create a new SQL Server Login for the visitor id database to use to connect to the visitor id database.

1. Open Microsoft SQL Server Management Studio:
   1. via Start Menu:
      1. Click ‘Start’ and type ‘SQL Server’.
      2. The results should show ‘Microsoft SQL Server Management Studio xx’. Click this result item.
   2. OR via File Explorer (for Microsoft SQL Server Management Studio 18):
      1. Open Windows File Explorer.
      2. Navigate to: C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE
      3. Double click the ‘Ssms.exe’ file.
2. Connect to the visitor id instance. Use the ‘sa’ login and password as described in the article Installing Microsoft SQL Server Manually.
   
3. Expand the ‘Security > Logins’ nodes by clicking the ‘+’ signs:
   
4. Right click the ‘Logins’ node and select the ‘New Login’ menu item:
   
5. In the ‘Login – New’ window:
   1. Type ‘visitorid’ in the ‘Login name’ field.
   2. Choose the ‘SQL Server authentication’ option.
   3. Enter a password in the ‘Password’ and ‘Confirm password’ fields. The default password for the ‘visitorid’ login as setup in the master installer is ‘visitorid1$’.
   4. Untick the ‘User must change password at next login’ and ‘Enforce password expiration’ checkboxes.
   5. Press the ‘OK’ button:
      
6. Open the ‘Step 3 – Change Database Owner.sql’ file in Microsoft SQL Server Management Studio.
7. Keep the script as-is unless your visitor id database name is different. If your visitor id database name is different then you will need to change the ‘USE visitorid’ line to match the correct visitor id database name. Press the ‘Execute’ button:
   
8. You should see a success message. Press the ‘x’ button to close the script window and continue to the next step. You do not need to save the SQL script when closing. If you receive an error message then please take a screen shot and send an email to support@visitorid.com.au:
   
9. Exit Microsoft SQL Server Management Studio.

Step 4: Apply SQL Server Configuration Settings for Client Connections

1. Open Microsoft SQL Server Configuration Manager:
   1. via Start Menu:
      1. Click the ‘Start’ button and type ‘SQL Server’.
      2. The results should show ‘SQL Server xxxx Configuration Manager’. Click this result item.
   2. via Microsoft Management Console:
      1. Click the ‘Start’ button and type ‘mmc’.
      2. The results should show ‘mmc – Run command’. Click this result item.
      3. Accept any UAC prompts.
      4. In the ‘Management Console’ window, click the ‘File > Add/Remove Snap-in…’ menu item:
         
      5. In the ‘Add or Remove Snap-ins’ window, on the left-side pane click the ‘SQL Server Configuration Manager’ item, then click the ‘Add >’ button, then click the ‘OK’ button:
         
      6. Press the right-arrow next to the text ‘SQL Server Configuration Manager’ item in the left-pane under ‘Console Root’ to expand the node:
         
2. Expand the ‘SQL Server Network Configuration’ node, then click the ‘Protocols for VISITORID’ item:
   
3. Right click the ‘TCP/IP’ item in the right-pane and click the ‘Enable’ menu item. Click ‘OK’ on the warning message that appears:
   
4. Using the same technique, make sure ‘Named Pipes’ is disabled and ‘Shared Memory’ is enabled. The final result of this window should be as in this image:
   
5. Expand the ‘SQL Native Client 11.0 Configuration (32bit)’ node, then click the ‘Client Protocols’ item:
   
6. Right click the ‘Named Pipes’ item in the right-pane and click the ‘Disable’ menu item:
   
7. Repeat steps 5 and 6 for the ‘SQL Native Client 11.0 Configuration’ node:
   
8. Click the ‘SQL Server Services’ node in the left-pane, then right click the ‘SQL Server (VISITORID)’ item in the right-pane and click the ‘Restart’ menu item:
   
9. Right click the ‘SQL Server Browser’ item in the right-pane and click the ‘Restart’ menu item:
   

Step 5: Update the Windows Firewall for Microsoft SQL Server Client Connections

1. Right click the ‘TCP/IP’ item in the right-pane and click the ‘Properties’ menu item:
   
2. In the ‘TCP/IP Properties’ window Click the ‘IP Addresses’ tab and scroll to the bottom of the list. Make a note of the ‘IPAll > TCP Dynamic Ports’ value:
   
3. Click the ‘Start’ button and type ‘firewall’. Click the ‘Windows Firewall’ result.
4. In the ‘Windows Firewall’ window click the ‘Advanced Settings’ item in the left-pane:
   
5. In the ‘Windows Firewall with Advanced Security’ window click the ‘Inbound Rules’ item in the left-pane, then click the ‘New Rule…’ item in the right-pane:
   
6. In the ‘New Inbound Rule Wizard’ window ‘Rule Type’ screen, choose the ‘Port’ option then click the ‘Next >’ button:
   
7. On the ‘Protocol and Ports’ screen, enter the default Microsoft SQL Server ports as well as the Dynamic Port as noted in step 2 with commas in-between each value as in this image, then click the ‘Next >’ button:
   
8. On the ‘Action’ screen, make sure the ‘Allow the connection’ option is selected then click the ‘Next >’ button:
   
9. On the ‘Profile’ screen, make sure all three checkboxes are ticked then click the ‘Next >’ button:
   
10. On the ‘Name’ screen, type ‘visitor id Microsoft SQL Server Ports’ in the ‘Name’ field, then click the ‘Finish’ button:
    
11. Close the ‘Windows Firewall’ window and close the ‘SQL Server Configuration Manager’ window.