Database - Setup MS SQL Server

This article outlines how to have EZSurvey (Pro version only) interact with Microsoft SQL Server and submit user data to Microsoft SQL Server with immediate data collection. It is best to create a new Login in SQL Server. The two sections of this article are SQL Server and EZS CGI on the same server. and SQL Server and EZS CGI on the different servers.

SQL Server and EZS CGI on the same server:

This is an example of how to set SQL Server and EZSurvey.

  1. Open SQL Server Enterprise Manager and select Database. Right click Database and select New Database. Enter the new database name. In this case we are calling it ezstables. You should see that a new database has been created.
  2. Creat a Login account for SQL Server. Select Login under Security. Right click Login and select New Login.
  3. On the Login Properties window on the General tab, select the ... button next to the Name box.
  4. A list of names will appear. Select the Internet Guest Account. It will be named something like IUSR_COMPUTERNAME, where COMPUTERNAME is the name of your computer. In this case the computer name is COMPUTERNAME. When you have done this, press Add and then OK.
  5. You will be taken back to the Login Properties window. Select Windows Authentication and select the Default Database.
  6. Select the Database Access tab and allow permissions on the database called ezstables Press OK
  7. You will now see a new Name in the list of Logins. In this case it is COMPUTERNAME\IUSR_COMPUTERNAME
  8. Expand the database you are working with. Select Users. If the internet guest account user is not listed right click and select Users and select New Database User.
  9. Right click on the ezstables database and select Properties. Then select Permissions. Allow the new account to be able to Create Table. Press OK.
  10. After EZSurvey Pro creates a table in the SQL Server database, select the table. Right click it and select Properties.
  11. In the Object Properties window allow the user to INSERT. Press OK.

Create a Data Source Name Entry.

  1. Go to the ODBC Data Source Administrator. It is normally found in the Administration Tools. Go to the System DSN tab and select Add.
  2. The Create New Data Source window will open. Select SQL Server. Press Finish.
  3. Type in the name that you want to give the Data Source. In this case it is called ezstables. Select (local) for the Server. Press Next.
  4. Select With Windows NT authentication using the network login ID. Press Next.
  5. For Change the default database to:, select the database that was created in SQL Server. In this case it is ezstables. Press Next.
  6. The next page will look like this. Press Finish.
  7. When you run a Data Source Test, it will show you the TESTS COMPLETED SUCCESSFULLY! message.
  8. When you are done, you should see the new System Data Source entry listed.

Connect EZSurevy Pro Desktop top the ODBC Data Source.

  1. After you have created the Data Source, go into EZSurvey Pro and select File Properties Database. In the Database Properties window, remove the Database file name. For the Database type, select ODBC. Press Save. The Open Database window will show. Select Browse.
  2. The Select Data Source window will open. Go to the Machine Data Source tab and select the Data Source that you created. Press OK
  3. In the Table name box, type a new name for the table. Then Open.

Connect EZSurvey Pro CGI to the ODBC Data Source.

  1. Go to the Web form setup window by selecting Publish Create a web form and configure a site to publish the form on. Select Save to a database with ODBC. Press Setup.
  2. Type in the name of the Data Source, in this case it is EZSTables. Type in the name of the table. Press Save. Then press OK on the Web form setup window to publish the form.

SQL Server and EZS CGI on different servers:

To connect 3rd party CGI's to SQL Server for the purpose of inserting records, follow these instructions.

On the SQL Server:

  1. Open SQL Server Enterprise Manager.
  2. Select Tools SQL Server Configuration Properties Security.
  3. For Authentication, select SQL Server and Windows. Select OK.
  4. Select the server node then Security Logins.
  5. Create a new login with SQL Server Authentication and set the password.
  6. Select Database Access and Permit the database to be used by the new login.
  7. Select OK.
  8. After the table is created, permissions need to be set for the login to access the table.
  9. Select the Permissions on your table.
  10. Enable insert permission for your user.

On the Web Server:

  1. Create a new System DSN entry with the SQL Server Driver.
  2. Connect the DSN to the SQL Server computer where it says Server:.
  3. Select With SQL Server authentication using a login ID and password entered by the user.
  4. Select Connect to SQL Server to obtain settings for the additional configuration options.
  5. Enter the Login ID and Password from the user that was created on the SQL Server computer.
  6. Select Change the default database to: and select your database.

See also...