Help Configuring MS SQL 2005 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 ODBC Remote Connections Configuration

Remote Access Part 2 Allow the remote Connections
Remote Access Part 3 Services Startup Mode

 

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 next 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!
SQL Server Browser

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?

back to top

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…
(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.
ALTER
LOGIN sa ENABLE ;
GO
ALTER
LOGIN sa WITH PASSWORD = ‘<password>’ ;
GO
============================================
That should get you up and running.
John Griffiths