Connect to MSSQL Server

  1. 6 days ago

    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.

    Open to suggestions.

  2. natale p

    Nov 6 Pre-Release Testers, Xojo Pro

    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.

  3. Sascha S

    Nov 6 Pre-Release Testers, Xojo Pro Germany, Lower Saxonary

    @natalepappalardo 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.

    Same here. Get MBS and forget about such issues once and for all... :)

  4. Julian S

    Nov 6 Pre-Release Testers, Xojo Pro UK
    Edited 6 days ago

    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) :

    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
    
    db1 = New MSSQLServerDatabase
    db1.Host = "192.168.0.1\SQLEXPRESS"
    db1.Port = 1433
    db1.DatabaseName = "DBNAME"
    db1.UserName = "USERNAME"
    db1.Password = "PASSWORD"
    db1.Timeout = 3
    db1.Connect
    
    db2 = New SQLiteDatabase
    db2.DatabaseFile = New FolderItem("C:\Program Files\Xojo\Xojo 2019r2\Example Projects\Database\SQLite\EddiesElectronics.sqlite")
    db2.Connect
    
    db3 = New MySQLCommunityServer
    db3.Host = "192.168.0.1"
    db3.UserName = "USERNAME"
    db3.Password = "PASSWORD"
    db3.DatabaseName = "DBNAME"
    db3.Connect
  5. Nedi F

    Nov 7 Pre-Release Testers, Xojo Pro Europe (Italy, Bologna)

    Hi Dean, did you try to connect to your database using Microsoft SQL Server Management Studio?

  6. 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;

    Driver={SQL Server Native Client 11.0};Server=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.

  7. Yes, no problem connecting using Microsoft SQL Server Management Studio.

  8. Julian S

    Nov 7 Pre-Release Testers, Xojo Pro UK
    Edited 6 days ago

    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};

  9. No odd characters, only upper and lower case letters and numbers. I'll try curly braces anyhow.

  10. Julian S

    Nov 7 Pre-Release Testers, Xojo Pro UK

    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)

  11. Julian S

    Nov 7 Pre-Release Testers, Xojo Pro UK

    Also, what version of xojo are you using?

  12. 2019r2

  13. 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.

  14. Julian S

    Nov 7 Pre-Release Testers, Xojo Pro UK

    Try the driver from https://www.microsoft.com/en-us/download/details.aspx?id=56567 and tweak your driver string to use it and see if that helps.

  15. Christian S

    Nov 7 Pre-Release Testers, Xojo Pro, XDC Speakers, Third Party Store Germany

    Does it work with MBS Xojo SQL Plugin ?
    If you have trouble there, I can maybe help you.

  16. 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.

    Looks like I may not have a choice though.

  17. Christian S

    Nov 7 Pre-Release Testers, Xojo Pro, XDC Speakers, Third Party Store Germany

    if you use SQLDatabaseMBS class, the changes are minimal.

    Especially as 19.5 plugin version will add more for API2 functions, so it's more compatible.

  18. So starting an MBS conversion and the first hiccup is TableSchema isn't supported. What's the alternate?

  19. Christian S

    Nov 7 Pre-Release Testers, Xojo Pro, XDC Speakers, Third Party Store Germany

    Make the right SQL query to lookup table schema for your database type!?

  20. MSSQL - I'm googling for the right SQL statement.

or Sign Up to reply!