ODBC Read-Only Access

ODBC Read-Only Access

Do not implement without permission from Eric/Wes

Steps to enable ODBC Access

Prepared by Dave Lesage.

To create a local-network ODBC connection:

1. Login to SSMS using owner / windows credentials
   1. Connect to (local)
   2. Right click (localhost) database
      1. Properties
         1. Security
            1. Ensure that Server Authentication is set to SQL Server and Windows Authentication mode
            2. Click OK
            3. Note: If this setting needs to be changed, a restart of the MSSQL service will most likely be required
   3. Expand Security
   4. Right-click Logins
      1. New Login
         1. General
            1. Choose login name
            2. Select SQL Server Authentication
            3. Enter a strong password (put both in LMI)
               
            4. Uncheck enforce password expiration (since the user will not be logging in directly)
            5. Set Default database to ClubspeedV8 (or the eurekas database, depending on customer needs)
         2. Server Roles
            1. Make sure only public is checked
         3. User Mapping
            1. Check ClubspeedV8 up top
            2. Check public down bottom
            3. Check db_datareader down bottom
            4. Leave all other items unchecked (uncheck "owner" on bottom)
               
            5. Repeat for Eurekas if needed
         4. Click OK
2. Open SQL Server Configuration Manager
   1. SQL Native Client 10.0 Configuration
      1. Client Protocols
         1. TCP/IP (double click it)
            1. Default port should be 1433
            2. Enabled should be yes
            3. Note: If either of these settings need to be changed, a restart of the MSSQL service will most likely be required
3. Open ODBC Manager (performed on computer connecting to MSSQL, see Side note below)
   1. System DSN
      1. Add
         1. SQL Server Native Client (any version should be fine, 10.0 as a fallback)
         2. Click Finish
         3. Name: ClubspeedV8 (or whatever, as long as the customer knows what the name is)
         4. Server: {IP_Address, no port unless the port is something other than 1433}
         5. Click Next
         6. Switch to "With SQL Server authentication..."
         7. Login ID: {Username created earlier}
         8. Password: {Password created earlier}
         9. Click Next
         10. Check Change the default database to, set to ClubspeedV8 (or eurekas, as necessary)
         11. Switch Application Intent to READONLY
         12. Click Next
         13. Click Finish
         14. Click Test Data Source, make sure the connection is valid
         15. Click OK 3x

ODBC Manager Side Note

There are two versions of the ODBC Manager. If a 32-bit application is going to be used, then C:\Windows\SysWOW64\odbcad32.exe should be used, otherwise C:\Windows\System32\odbcad32.exe should be used for 64-bit applications. The interface should be the same for both versions. Also, the ODBC should be set up on the client machine (or external server -- whichever machine is going to be connecting to SQL server)

Related Articles