Working SQL 2000 config??

[wayne106]

1. Brekeke Product Name and version: BSS 2.0 latest download (eval)

2. Java version: jre1.6.0_04

3. OS type and the version: Win2k3 svr web edition

4. UA (phone), gateway or other hardware/software involved: none

5. Select your network pattern from http://www.brekeke-sip.com/bbs/network/networkpatterns.html :

6. Your problem:

Trying to get BSS to register to my MS SQL server 2000 DB have tried both latest JDBC driver :- http://msdn2.microsoft.com/en-us/data/aa937724.aspx

and an older one that seemed to work for someone else:-
http://www.brekeke-sip.com/bbs/viewtopic.php?t=2475

But getting nowhere fast! the biggest problem is when I input the DB config in for the first time, BSS seems to take it and connect ok, but when I try to start sip server, I get popup (port error) Then Database connection errors.

When I look in the log.log the first time, it shows it has connected to my server, but there after even though no config changes have been made, it shows that its tried to connect to 127.0.0.1 and not even tried my server!!

Heres sv.properties:-
#SIP Server properties file
#Thu Jan 31 22:01:34 GMT 2008
net.registrar.upper.url=
net.usrdir.plugins=SqlUserDir
net.sip.addrecordroute=on
net.rtp.video.port.max=0
net.rtp.loglevel.console=0
alias.url=
net.upnp.use=false
net.auth.realm=bss01
net.net1.interface=
userdir.url=jdbc\:sqlserver\://10.0.0.1\:1433;DatabaseName\=serverdb
sipadmin.net.upnp.timer.period.sec=30
net.registrar.thru.allow=off
alias.password=
net.rtp.port.min=10000
alias.enabled=off
net.registrar.loglevel.console=0
sipadmin.productname=
ua.loglevel.console=0
net.rtcp.loglevel.console=0
net.registrar.thru.nat.allow=true
net.upnp.timer.use=true
net.register.loglevel.console=0
sipadmin.net.portmap.cache.period.sec=86400
net.portmap.cache.period=86400000
networkaddress.cache.ttl=3600
net.net4.interface=
sv.location=UK
com.brekeke.sipadmin.startparam1=
net.sip.adjustif=off
net.registrar.upper.catchhere=true
net.sip.ipfilter.pattern=
alias.user=
userdir.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
net.sip.port.local=5060
net.auth.policy4=off
sipadmin.startup=auto
net.auth.policy3=off
net.auth.policy2=off
net.sip.res100.all=on
net.portmap.cache.size=24
net.rtp.port.max=10999
net.userdir.cryptpw=true
net.sip.addrport=on
userdir.enabled=on
net.auth.request=3
alias.driver=
net.net2.interface=
net.sip.followroute.nat=false
userdir.password=password
net.auth.request_r=on
sv.description=SIP server
net.auth.loglevel.console=0
net.auth.policy=0
net.auth.request_i=on
net.sip.timeout.talking=259200000
net.listener.loglevel.console=0
net.ip-gw=
net.sip.addrport.recv=off
net.net5.interface=
net.registrar.thru.catchhere=true
net.userdir.loglevel.console=0
registered.user=sa
embedded.port=
userdir.user=sa
sv.admin.sip=101@testsite
net.external-addr.pattern=
sv.name=Sip101
net.sip.timeout.ringing=240000
net.rtp.session.timeout=300000
net.registrar.upper.nat.allow=true
net.rtp.responseport=on
net.rtp.relay.localhost=on
net.registrar.awake.use=on
net.portmap.cache.use=true
net.sip.ipfilter.type=disable
registered.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
net.sip.ipfilter=false
net.rtp.video.port.min=0
registered.enabled=on
net.registrar.maxsession=100
registered.password=password
net.registrar.upper.timeout=30000
mng.loglevel.console=0
net.registrar.awake.interval=12000
sv.loglevel.console=0
sv.admin.mail=
net.sip.loglevel.console=0
net.net3.interface=
net.session.limit=-1
net.upnp.port.exact=false
net.registrar.upper.allow=off
net.upnp.timer.period=30000
registered.url=jdbc\:sqlserver\://10.0.0.1\:1433;DatabaseName\=serverdb
net.rtp.relay=on


Log.log :-
2008-01-31 22:01:36,531 [Thread-1] ERROR (?:?) -
com.brekeke.sipadmin.alias.AliasDBException: Connection error [b]jdbc:hsqldb:hsql://127.0.0.1; prop={user=sa, enabled=off, password=, url=, driver=}
at com.brekeke.sipadmin[/b].alias.SqlAliasDB.getConnection(Unknown Source)
at com.brekeke.sipadmin.alias.SqlAliasDB.initConnection(Unknown Source)
at com.brekeke.sipadmin.alias.SqlAliasDB.open(Unknown Source)
at com.brekeke.sipadmin.alias.SqlAliasDB.<init>(Unknown Source)
at com.brekeke.sipadmin.SipAdminBase.initDatabase(Unknown Source)
at com.brekeke.sipadmin.SipAdminBase.initApp(Unknown Source)
at com.brekeke.sipadmin.web.Initializer.initialize(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.brekeke.web.GateServlet.init(Unknown Source)
at javax.servlet.GenericServlet.init(GenericServlet.java:211)
at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1091)
at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:925)
at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:3880)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4141)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:759)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:739)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:524)
at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:804)
at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:693)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:472)
at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1118)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:310)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1020)
at org.apache.catalina.core.StandardHost.start(StandardHost.java:718)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1012)
at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:442)
at org.apache.catalina.core.StandardService.start(StandardService.java:450)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:680)
at org.apache.catalina.startup.Catalina.start(Catalina.java:536)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:275)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413)

Can someone point me in the right direction?

Thanks

Wayne

[voipwell.com]

Wayne,

You got real close with the following differences:

userdir.url=jdbc\:sqlserver\://10.0.0.1:1433;DatabaseName\=serverdb
userdir.url=jdbc\:microsoft\:sqlserver\://127.0.0.1:1433;DatabaseName\=serverdb

userdir.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
userdir.driver=com.microsoft.jdbc.sqlserver.SQLServerDriver


registered.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
registered.driver=com.microsoft.jdbc.sqlserver.SQLServerDriver

registered.url=jdbc\:sqlserver\://10.0.0.1:1433;DatabaseName\=serverdb
registered.url=jdbc\:microsoft\:sqlserver\://127.0.0.1\:1433;DatabaseName\=serverdb

If you later switch to sql server 2005 there is even a different syntax. This should fix you up. I think your 10.0.0.1 will work instead of 127.0.0.1 but 127.0.0.1 works for me.

Nick

[wayne106]

Hi Nick, thanks for that, I tried your driver name, but the error changes to Driver error, when I put it back to

com.microsoft.sqlserver.jdbc.SQLServerDriver

the error changes to connection error.

I looked through the samples included with the JDBC driver, and their sample URL does not contain microsoft so :-
jdbc:sqlserver://10.0.0.1

but if I do this then I get the
Connection error jdbc:hsqldb:hsql://127.0.0.1
error
BTW: my SQL server is not local, its on IP 10.0.0.1 which is not the BSS IP - I have checked connectivity though using the statndard ODBC connection manager and all is ok.

If I revert to your URL then at least it looks like it tried in the log, just connection error:-

2008-02-01 19:44:35,296 [http-18080-Processor24] ERROR (?:?) -
com.brekeke.net.sip.sv.registrar.RegisteredDBCanNotOpenException: Connection error jdbc:microsoft:sqlserver://10.0.0.1:1433;DatabaseName=serverdb; prop={user=sa, url=jdbc:microsoft:sqlserver://10.0.0.1:1433;DatabaseName=serverdb, password=password, enabled=on, driver=com.microsoft.sqlserver.jdbc.SQLServerDriver, class=com.brekeke.net.sip.sv.registrar.plugins.RegisteredSqlDB}

I wonder if you or anyone has a working SQL 2000 databse, with the JDBC version (so I can d/l it) and the correct settings because it looks like this is a bit of a settings l o t t e r y!!! my weak Java knowledge is not helping. Thanks again,

Wayne

[voipwell.com]

Wayne

What i gave you is a working sql server 2000 config.

For sql 2005 i use this
userdir.url=jdbc\:sqlserver\://localhost\:1433;DatabaseName\=mydb

Perhaps you downloaded the sql 2005 java applet from microsoft instead of the sql 2000.

[wayne106]

Hi Nick, you are right I was using the 2005 version this is what Brekeke had recommend I use, as its backward compatible with 2000, however it seems BSS doesn't recognise either the URL or the driver.

Anyway I have gone back to a SQL 2000 only version and we have progress, BSS sip service has logged onto the SQL server (I can see this using profiler) and I have created some users and they are registering, however the admin tool, is still saying DB connection failed check settings or reboot machine. I can still stop and restart BSS sip service and make calls, just no front end, I presume that the admin tool is still supposed to work when you are using SQL, have you seen this before? I thought they used the same SQL connection but its seems not.

This maybe where I have changed SQL JDBC drivers so many times and played with the config files, where do I start?

Thanks

Wayne

[voipwell.com]

Wayne,

Great news to hear you are making progress. I looked at your original post and you were good enough to put your java version in the header. You need to dump the java 1.6 as i can confirm it simply does not work with the pbx. go back to 1.5.0.13 or so. I have heard 1.6 works with the sip server by itself but the pbx was designed to work with 1.5 and it will not work with 1.6. I think you will need to remove java 1.6, install java 1.5 then remove brekeke and reinstall brekeke. You can google "sun java previous versions" for the previous java. That should get you on your way.

Oh, yes, when using a third party database you can still add, remove and change sip users with the sip authentication screen in brekeke. It works exactly the same. You can also add more fields to the tables and use your own sql tools to edit the added fields.

Nick

[wayne106]

Hi Nick, I did as suggested, removed java and BSS rebooted, installed Java 1.5.14 and then BSS, and exactly the same problem!

When I look in the log I see this:-
com.brekeke.sipadmin.alias.AliasDBException: Connection error jdbc:hsqldb:hsql://127.0.0.1; prop={user=sa, enabled=off, password=, url=, driver=}
at com.brekeke.sipadmin.alias.SqlAliasDB.getConnection(Unknown Source)

So its not even trying - don't where 127.0.0.1 is coming from,
but BSS sip server is connecting correctly to my SQL server no problem and working! very bizarre. do you think 1.5.14 is early enough, it was the earliest 1.5 I could find on Java website.

Wayne

[voipwell.com]

Wayne,

The log appears to indicate the sip server is still trying to use the hsql database that is native to brekeke, while actually using the sql. I wonder if a table or field is missing from your sql database or if a datatype is wrong on one of the fields. I just don't know what conditions would cause that. I would start over and check to make sure you have the data tables correct, the keys correct and the data types in the tables correct. You must create all the tables and fields that brekeke lists in their 3rd party db document. I've had no problem with it. All it takes is one thing wrong and well you know.

Another hunch i have is you used the 1.6 java on install so when the brekeke and db meshed it was using the wrong java. Believe me 1.6 causes problems. I did extensive testing with it. I think if you wipe the install off your system and reinstall with java 1.5 and the correct sql version applet you will be good to go.

[wayne106]

Hi Nick,
I am now running 1.5.6 and after much messing about, I have got the whole thing working on 2000 server. I then lifted the config and reinstalled everything on my live 2003 server box (web edition) and same problem, Sip server takes the config, and runs but the front end doesn't work, so it looks like a problem with 2003 server and MSSQL. Very frustrating! I can either have it all working but local DB, or just the sip server working with SQL.

I could run BSS on 2000 but I am concerned about security; 2k3 all updated using integrated FW is pretty good (for a MS product!) I would need a third party FW on 2000

Thanks for your help anyway.

Wayne

[james]

Hi All,

Brekeke SIP Server's latest version can handle this situation.
Let you try it.

[wayne106]

Hi, I upgraded it and its working! So thanks for your and Nicks help, I'm guessing that Brekeke found the bug and fixed it.

Wayne