SQL Connection Setup Tips for Clarion Programmers

Help Configuring MS SQL Express to work with a Clarion For Windows exe
and
getting a Remote Connection working.

This document outlines several things that you can check and configure to connect a CW program to a MS SQL Server/ MS SQL Express Server.

Remote Access Part 1

Remote Access Part 2

Remote Access Part 3

Remote Access Part 4 - TCP-IP

Authentication Mode sa Activation

Remote Access (Part 1)

To allow remote connections (AND also local connections using ODBC as you do when connecting from a CW program) ... you need to turn on local and remote connections. This is done with the SQL Server Surface Area Configuration.

Note: if you used the setup switch ..

DisableNetworkProtocols=0

.. during the install, then the setting should be OK and you will not need to perform the setting change described here.

The SQL Server Area Config needs to be run to enable "remote" connections to the SQL engine.

Even if it is running on the same physical PC, and uses ODBC then it is deemed a "remote" connection to the database.

so choosing the

gives this screen.

Now we CAN turn on remote connections..

Hit [Apply] and [OK]

Remote Access Part 2

++ Another thing to check is the property screen within SQL Server Management Studio Express. Right-Click on the Server name in the Object Explorer screen, and select Properties. Then select the Connections (1a) section. Ensure that "Allow remote connections to this server" is checked [x]. 1b

Remote Access Part 3

Check on the Server that the service SQL Browser IS running. Right-Click "My Computer" and select Manage. Expand the Services and Applications tree, and then the SQL Server configuration Manager. Select the SQL Server 2005 Services and ensure that both the SQL Server and the SQL Server Browser are running. If not, then you will not connect!

If you have changed any network configuration settings for the SQL Server, it should be stopped and restarted. In the above screenshot, you can simply right-click the SQL Server and select ReStart. This may not work if you do not have sufficient rights.

FireWall

If you are still having problems connecting to your database, try testing with the firewall turned off temporarily. Remember to turn it back on after testing.
Also, see if you can access the Server using the Windows Explorer/My Network Places/.....

The files you need to add to the Exceptions list in your Firewall settings on your Server are: sqlservr.exe and sqlbrowser.exe

Maybe I should have placed this first in the list of things to check, but hey, you are a programmer right? And the Server IS running, right?

Remote Access Part 4 - TCP-IP
: ( updated July 2008 ) .. So you need to connect from a remote site across the network (LAN or WAN) using just an IP address and Port.. such as 222.111.222.111,1433 Watch this space...; SQL Connection String with IP Addressing

Lets assume that the IP Address for your Server is 192.168.1.30

and that SQL Server is now listening on that IP at Port 1411

You want to connect to the database NorthWind

The connection UserName with SQL Authentication is Fred

and Fred's password is pa$$word

The connection string you would build would look like...

"192.168.1.30,1411",NorthWind,Fred,pa$$word

Note the IP address,port is enclosed in double quotes because of the embedded comma.

Authentication Mode

If you want to use Mixed Mode Authentication, (so you can logon with a SQL username and password i.e. the sa logon) then you may need to reset the Authentication mode. By default, whilst installing Windows Authentication mode is set.

For this you need the SQL Server Management Studio Express installed. It may already be on your menu...

To get the Studio Express from Microsoft, the MS download link is...

http://go.microsoft.com/fwlink/?linkid=65110

(about 43 Megs)

(This requires MSXML 6.0 which may already be on Windows machine if you have recent updates.

Once Studio Express is installed, only then can we switch on the MixedMode connections to the database...

Start StudioExpress, right-click the Server in the left hand pane, and select properties to get this screen..

Set the Server Authentication as shown above.

That should be all that is needed, apart from a Stop and restart of the service.

The sa User activation

If whilst installing, Windows Authentication mode only was set, you now need to enable the sa user. This user was created during the install, but was set to disabled.

Open a New Query window (button just above the Object Explorer left side) and enter the below sql commands.. but of course replace the <password> shown here with a strong password of your own. Make sure it can be found/remembered when needed.