Self Service and MSSQL

[terryd]

If I use Self Service in a website using TPS files everything works, on restart of the server the website is accessible.
If I convert the app to using MSSQL and start without the service the application works and website is accessible.
If I start the webservice the sevice shows in services as running but the website isn't accessible.
Stop and start the service and the website is accessible.
Has anyone run a service using MSSQL and got any tips as to what I can try?

[peterH]

Hi Terry,
Sounds like a dependency thing. Tell the service to wait for MSSql to start, then you should be ok.

Peter

[terryd]

Hi Peter
Thanks
How do I do that?

[peterH]

Not sure, haven't done it myself in SS but on the Global Extensions, Options Tab, there's a prompt at the very bottom. It also refers to the documentation.

Peter

[terryd]

Thanks Peter
If I look in the documentation it says that it should be the service name of the dependancy. In services.
The service name of the MSSQL Server is MSSQL$SQLEXPRESS which I put into the field and compiled.
I then deleted the existing service (cmd sc delete _pdweb) andI reran the application clicked install and start as a service.
The service is shown as running but the website is still not accessible.
If I stop and start the service the website is accessible.
Hs anyone got the webserver working with MSSQL and self service?

[osquiabro]

 terryd, i have a webserver with SQLServer but not SelfService, use ABCFree and NT Service Extension, 3 companies running this settings over 3 year without problems.

[terryd]

Thanks
I would prefer to use SelfService if it's possible since I don't know how far forward ABCFree will go.
If CapeSoft can't give me a solution I suppose I'll have to go that way.

[Bruce]

>> Has anyone got the webserver working with MSSQL and self service?

yes, we've got a few sites with this configuration - so it's definitely possible.

You can manually add a dependency to a service using the Services.Msc panel - so that would be the first thing to try. It certainly sounds like a dependency issue, and I'm guessing setting it manually will at least test out that theory. If that works one can re-visit your SelfService setting.
If it doesn't work then we need to look elsewhere for the solution.

cheers
Bruce

[terryd]

Hi Bruce Thanks for the reply.
I restarted the wervice and check in the services.msc
The service is there. I checked under dependencies and SQLExpress is there so the tab in SelfService did add the dependencies as expected.
I used the web55 example to test the whole process.
Its a bit long winded but hopefully something I am doing wrong can be pointed out

Compiled and ran Web55 example
On the Service Options tab clicked Install and Start service
application displays in services and the website is accessible
Added Global Variable GLO:OwnerName (s255)
Created a SQL copy of the MailBoxes table
Added FM3 into Global Extensions
Imported FM3 Connect procedure
In the extensions set the Global Owner Variable to GLO:OwnerName
Left the rest of the settings as is
In the properties of the Global FM3 extension AutoUp\SQL checked the MSSQL box and ensured that SQL_Connect is in the SQL Connect Procedure
pasted /MULTIPLERESULTSETS=TRUE /BUSYHANDLING=2 into the Driver string for connect field.
Use the wizard to create a netweb browse on the MailboxesSQL table and attach it to the Index page   
Create a database in MSSQL called SSTest

Compile and run
In the SQ Connect backend
user:sa
password:
Use Windows Authentication
Server TERRY-PC\SQLEXPRESS
database: SSTest
Application is active and an empty browse displays the sql table
SelfService Global extension Options tab Single Dependency: Mssql service name = MSSQL$SQLEXPRESS
Compile
In Service options Click Install and Start as a service. Shows as running in services and the window disappears as expected.
Access the website Message Unable to connect
Recompile and remove from services
Access website: success
This may be due to multiple dependencies but I cannot find any using dependencyWalker.






 

[terryd]

The attached is the above test just before installing and running the service
The only changes required would be to create the database change the SQLConnect string info and to change the dependency name in the SelfService options if you are not using SQLEXPRESS.


[attachment deleted by admin]

[kevin plummer]

Hi Terry,

try without "Use Windows Authentication" and just use the user sa and sa password when running as a service. I assume you have it set to auto login on the sql_connect procedure?

Failing that, I would drop in some debugview points to see how far it is getting.

[terryd]

Hi Kevin
Thanks I'll give it a go and come back with the results

[walter.dasilva]

Hi Terry,

Are you using multi-dll or one piece .exe?

[terryd]

HI Walter one piece exe with dll. This is the standard self service example with sql added
In my own application the configuration is one piece exe with lib.

[Larry Sand]

We use MS SQL with NTWS and Self Service at thousands of sites.  If you want to capture debugview output from the service you must run debugview as admin and check "Capture|Capture Global Win32" to see things from your service.   Modern windows has service hardening and runs the service in a different ring.  The local service account is somewhat restricted, but you can set the service to run under a user.  Then you can adjust the rights granted to the user running the service.  That's what we do on all of our installs, the SB installer creates a web server user and grants the rights the service needs and then we assign that user to service.

Hope that helps,

Larry Sand