2

I'm learning rust and I've written my first very simple API using Rocket. Now I'd like to connect my server to an existing database I've got on MSFT Azure. I'm having a hard time finding examples on how this works for Mssql, the SQLx repository only contains examples for Postgres and MySQL.

There is no connection string on the Azure Portal available for rust, so I've experimented with different versions for Go and ODBC:

const SERVER: &str = "<mysqlserver>.database.windows.net";
const PORT: &str = "1433";
const USER: &str  = "sqladmin";
const PASSWORD: &str  = "<mypassword>";
const DATABASE: &str  = "<mydatabase>";

MssqlConnection::connect(&format!("server={};user id={};password={};port={};database={};", SERVER, USER, PASSWORD, PORT, DATABASE)[..]).await?;

This gives an Error: Configuration(RelativeUrlWithoutBase) so I think SQLx expects a connection string like "postgres://...", I can't find this string for mssql though.

picklepick
  • 1,370
  • 1
  • 11
  • 24
  • 2
    According to their Github, SQL Server support was added in [June 2020](https://github.com/launchbadge/sqlx/issues/116) and it does appear in the [documention](https://docs.rs/sqlx/0.5.9/sqlx/) under the name [mssql](https://docs.rs/sqlx/0.5.9/sqlx/mssql/index.html). – Thom A Nov 18 '21 at 11:30
  • 1
    Once again a basic doco search appears to yield the answer :| – Nick.Mc Nov 18 '21 at 11:46
  • @Larnu yes, that's why I would like to use it to connect to my db. However, the problem I'm having is *how* to connect to mssql. The connection strings provided by the azure portal all seem to be of a different format than SQLx expects. – picklepick Nov 18 '21 at 11:48
  • @Nick.McDermaid where is the answer? I've been searching the azure docs for a pretty long time. In case your referring to Larnu's answer, I ofc found these links, however none of them helped me find the right connection string. – picklepick Nov 18 '21 at 11:48
  • Connection strings are just arcane combinations of server name, database name, sometimes port number, authentication methods and credentials – Nick.Mc Nov 18 '21 at 11:56
  • But yes I understand your frustration when all you're looking for is a bit of special syntax code and the internet refuses to give it up.... I shall continue searching. – Nick.Mc Nov 18 '21 at 11:58
  • @Nick.McDermaid well yes, but this is exact my question: *what is* the "arcane combination" required by sqlx for mssql? I've tried reading the docs, but as someone who has been programming rust for less than a week they are quite dense. I now see your second comment, thank you I'm also searching... please let me know if you find something! :) – picklepick Nov 18 '21 at 11:58
  • Whatever you're using, it's mostly gibberish to me, but this seems to be the area that lets you specify host, database, login, password. Does it mean anything to you? https://docs.rs/sqlx/0.5.9/sqlx/mssql/struct.MssqlConnectOptions.html#method.host – Nick.Mc Nov 18 '21 at 12:00
  • I have tested setting these without success. The signature of the [connect](https://docs.rs/sqlx/0.5.9/sqlx/trait.Connection.html#method.connect) function leads me to believe that there should be a single URL to connect. This works for Postgres (tested) but I cant find this URL for mssql. – picklepick Nov 18 '21 at 12:06
  • I don't know why people insist on posting videos.... but this appears to be an example of connecting to SQL Server through ODBC https://drupaland.eu/article/connecting-rust-microsoft-sql-server-linux – Nick.Mc Nov 18 '21 at 12:06
  • Here's some examples connecting through JDBC and ADO.Net. But I'm not familiar with rust so I don't know how helpful this is https://docs.rs/connection-string/0.1.13/connection_string/ You've probably come across all of these already. – Nick.Mc Nov 18 '21 at 12:07
  • 1
    @Nick.McDermaid The video uses ODBC, I've used that before, but then I'm losing all benefits SQLx provides. And the other connections string's I've tried without success. It looks like such a trivial problem, but I just can't find the right string. Anyways, thanks for the help. – picklepick Nov 18 '21 at 12:16
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/239358/discussion-between-picklepick-and-nick-mcdermaid). – picklepick Nov 18 '21 at 12:16
  • @Nick.McDermaid ad revenue, of course. – AlwaysLearning Nov 18 '21 at 12:17
  • Sorry I can't give a direct answer. I feel your frustration! I probably can't help any further except to say: don't expect anything other than SQL Authentication to work. Good Luck – Nick.Mc Nov 18 '21 at 12:19
  • My reading of the documentation and issues for SQLx suggests that it doesn't currently have great support for SQL Server. An example connection string is visible in [MSSQL returns incorrect result for query #819](https://github.com/launchbadge/sqlx/issues/819). [Add TLS support for MSSQL connections #1200](https://github.com/launchbadge/sqlx/pull/1200) is concerning, suggesting that the SQL support may even be going closed source. – AlwaysLearning Nov 18 '21 at 12:28
  • @AlwaysLearning, I've just read the exact same issues :) From the snipped in #819 I created the following connection string: `mssql://:@:/;`. Looks like something is happening although queries are not working yet. – picklepick Nov 18 '21 at 12:56

1 Answers1

2

Although I'm still not able to connect to the SQL Server on Azure, the problem of finding the right connection string format has been solved. I was able to connect to a local server using:

MssqlConnection::connect("mssql://user:password@server/database").await?;

So the connection string follows the same schema as e.g. Postgres, it's unfortunate that none of the example strings on the Azure Portal are of this type.

Edit: The connection issues are most likely due to the lack of support for encrypted connections, more details here.

picklepick
  • 1,370
  • 1
  • 11
  • 24