I have tried every native Xojo way of connecting to a MSSQL Server on the network.
ODBC connections always result in a “Dialog Failed” error.
Utilizing the MSSQL Plugin results in a “Login failed” error. All my parameters are correct.
For testing I set up a MSSQL database on an Azure instance.
I can get a successful ODBC test from the ODBC control panel.
It appears as if I can get a successful connection using MBS SQL plugin.
I’d prefer to use native Xojo code but will go with MBS very soon since it works.
a few years ago, I made several attempts, with results that I do not remember well, but without success.
finally, I decided to try MBS and I solved.
I notice that nothing has changed, after a long time…
this is my experience, I don’t know if others have better results.
[quote=462164:@natale pappalardo]a few years ago, I made several attempts, with results that I do not remember well, but without success.
finally, I decided to try MBS and I solved.
I notice that nothing has changed, after a long time…
this is my experience, I don’t know if others have better results.[/quote]
Same here. Get MBS and forget about such issues once and for all…
Here’s a few different connection strings for different db types from a test project I was using to check for bugs, they all worked (details changed to protect the innocent). You’ll need to ensure that TCP/IP is enabled and listening on 1433 (google should turn that up) :
[code]db = New ODBCDatabase
db.DataSource = “Driver={SQL Server Native Client 11.0};Server=192.168.0.1\SQLEXPRESS,1433;Database=DBNAME;Uid=USERNAME;Pwd=PASSWORD;”
db.Timeout = 3
db.Connect
I’ve tried all manner of connection strings.
Here’s the last 4 I tried.
The first one is the connection string Azure tells me to use…
Server=tcp:afterten.database.windows.net,1433;Initial Catalog=afterten_Weather;Persist Security Info=False;User ID=USERID;Password=PASSWORD;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
Now just variations…
Driver={SQL Server Native Client 11.0};Server=tcp:afterten.database.windows.net,1433;Initial Catalog=afterten_Weather;Persist Security Info=False;User ID=USERID;Password=PASSWORD;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
Driver={SQL Server Native Client 11.0};Server=tcp:afterten.database.windows.net,1433;Database=afterten_Weather;Uid=USERID;Pwd=PASSWORD;
User ID and password are correct as they are used time and again to test the ODBC connection using Windows ODBC manager
and connect using MSSQL Server Manager
All have the same result - “Dialog Failed”
I have a test DSN set up on the machine that “Test Connection” reports as good. When I try to just use the DSN and fill in the ODBCDatabase.password (since ODBC doesn’t save the password) I still get Dialog Failed.
It seems that no where does Xojo pass the password to the ODBC Connection either by using the ODBCDatabase.password or by putting the password into the connection string.
Daft question, does your password contain any “odd” characters ? When using ODBC, I only get a Dialog Failed if my password is incorrect (yes it only works when passing the password in the datasource).
Try encasing your password in curly braces so Pwd={PASSWORD};
If that doesn’t work check there are no special hidden characters in there too, select the line, right click and select remove gremlins or something like that (on mobile can’t remember the exact working)
Curly brace around password, no help. Dialog failed.
Again I’m trying to connect to an Azure SQL instance, don’t know why it might be any different but just throwing it out there.
I can get a successful connection with MBS, haven’t tried to do an actual query yet. Was just trying to avoid re-writing code to use MBS database functions as wonderful as they are.
