[Sqlrelay-discussion] MSSQL Server login issue.

[keith <ke...@sc...>]

Hi, We want to put a SQL Relay server between our Web servers and a 
MSSQL 2005 server. We have a dedicated SQL Relay server and can start 
SQLRelay and can see it binding to an interface on port 1433 and can 
also see three connections going through to our MS SQL Server and it all 
looks good. But doesn't seem to be working.

 From a windows machine if we create a UDL File and try to make a 
connection directly to the SQL Server then we can. If we make a 
connection to the SQL server via SQL Relay we can't connect. We are 
trying to log in with user=user1 password=password1 although are not 
sure about what the database name should be so have tried a few and each 
one doesn't work, we have also tried getting the list of databases on 
the server but that doesn't work either.


If we use the UDL tool and point it to a non existing server / ip we get 
the following error.
=================================================================================
Test connection failed because of an error in initalzing provider.
[DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or 
access denied.

No other pop box appears!

Then if we point the UDL tool to the SQL Relay server we get the 
following error.
==============================================================================
[DBNETLIB][ConnectionRead(recv()).]General network error.
Check your network documentation.

If we click ok then another popup box appears with the following.

Login failed. Catalog information cannot be retrieved.


We know that SQL Relay is connection to the MSSQL Server as we can see 
that there are three connections to the specified database from SQLReay 
in the sql enterprise manager.


When we start sqlr with sqlr-start -id mssql     there is a message 
about the cache manager that we aren't sure about.

Starting cache manager:
   sqlr-cachemanager
Warning: using default id.

I have tried killing the process and restarting it with 
sqlr-cachemanager -id mssql and it starts but we are still unable to 
connect.


This is my sqlrelay.conf


<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">

<instances>

<!-- Regular SQL Relay Instance -->
<instance id="mssql" addresses="x.x.x.x" port="1433" 
socket="/var/run/keith.socket" dbase="freetds" connections="3" 
maxconnections="15" maxqueuelength="5" growby="1" ttl="60" 
maxsessioncount="10" endofsession="commit" sessiontimeout="600" 
runasuser="root" runasgroup="wheel" cursors="5" authtier="listener" 
handoff="pass" deniedips="" allowedips="" debug="none" 
maxquerysize="65536" maxstringbindvaluelength="4000" 
maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" 
listenertimeout="0" reloginatstart="no" timequeriessec="-1" 
timequeriesusec="-1">
<users>
<user user="user1" password="password1"/>
<user user="user2" password="password2"/>
<user user="user3" password="password3"/>
</users>
<connections>
<connection connectionid="db1" 
string="sybase=/etc/freetds.conf;user=thesqluser;password=thesqlpassword;server=sqlserver80;db=thesqldatabase;" 
metric="1"/>
</connections>
</instance>
</instances>


# cat /etc/freetds.conf
#   $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".

# Global settings are overridden by those in a database
# server specific section
[global]
         # TDS protocol version; the OpenBSD package defaults to 8.0 to
         # avoid plaintext passwords on the wire. You may set another 
default
         # here to communicate with certain types of server, or override it
         # on a per-server basis below.
;       tds version = 4.2

         # Whether to write a TDSDUMP file for diagnostic purposes
         # (setting this to /tmp is insecure on a multi-user system)
;       dump file = /tmp/freetds.log
;       debug flags = 0xffff

         # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

         # If you get out-of-memory errors, it may mean that your client
         # is trying to allocate a huge buffer for a TEXT field.
         # Try setting 'text size' to a more reasonable limit
         text size = 64512

# A typical Microsoft server
[egServer70]
         host = 192.168.1.2
         port = 1433
         tds version = 7.0

[sqlserver80]
         host = x.y.z.a
         port = 1433
         tds version = 8.0



# sqlr-start -id mssql

Starting listener:
   sqlr-listener -id mssql -config /usr/local/firstworks/etc/sqlrelay.conf

Starting 3 connections to db1 :
   sqlr-connection-freetds -id mssql -connectionid db1 -config 
/usr/local/firstworks/etc/sqlrelay.conf
   sqlr-connection-freetds -id mssql -connectionid db1 -config 
/usr/local/firstworks/etc/sqlrelay.conf
   sqlr-connection-freetds -id mssql -connectionid db1 -config 
/usr/local/firstworks/etc/sqlrelay.conf

Starting scaler:
   sqlr-scaler -id mssql -config /usr/local/firstworks/etc/sqlrelay.conf

Starting cache manager:
   sqlr-cachemanager
Warning: using default id.



Does anyone have any suggestions ?

Thanks
Keith