0

I am attempting to connect to Mssql server that is not local. I am using SQLx and am attempting to connect using the following:

use sqlx::mssql::MssqlPool;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> 
{
    let pool = MssqlPool::connect("mssql://server/db?trusted_connection=yes&driver=ODBC+Driver+17+for+SQL+Server")
                   .await?;
    
    let result = sqlx::query("select top 100 * from db.dbo.tbl")
                    .execute(&pool)
                    .await?;

    println!("{:?}", result);
    Ok(())
}

I omitted server and db names for obvious reasons. Everything builds fine, however I believe my connection string is incorrect. All examples from other questions I have seen require a user/pass, but given this is on company premises, I want to connect in a similar fashion as how I would in Python using pyodbc and the SQL Server driver installed on my machine.

The error when trying to run:

Error: Database(MssqlDatabaseError { message: "Login failed for user 'sa'.", number: 18456, state: 1, class: 14, server: "server", procedure: "", line: 1 })
Yuri Astrakhan
  • 8,808
  • 6
  • 63
  • 97
Coldchain9
  • 1,373
  • 11
  • 31
  • At risk of pointing out the obvious, something's not right here. The connection string you're using indicates Trusted Connection which means Windows authentication, but the error message is a failed attempt for the sa login which is a SQL login. – squillman Sep 12 '22 at 14:33
  • That's correct. I can't find any good documentation on Mssql in SQLx that specifies the proper string using trusted_connection. This is a production db at the company I work for and can connect to it fine using Python & trusted_connection but am having issues in Rust. – Coldchain9 Sep 12 '22 at 14:35
  • Got it. Sure seems SQLx is defaulting to the sa login, which is a terrible plan... – squillman Sep 12 '22 at 14:38
  • Unfortunately, nothing in the [docs](https://docs.rs/sqlx/latest/sqlx/mssql/struct.MssqlConnectOptions.html) suggests that any kind of support for integrated authentication has been implemented (and this is not completely trivial, so it's not unexpected). The [source](https://docs.rs/sqlx-core/0.6.1/src/sqlx_core/mssql/options/mod.rs.html) "helpfully" defaults to `sa` for the username, which further strengthens the idea that this was implemented by someone who doesn't exactly work with SQL Server for a living, since that's a bad choice even if you are using SQL authentication. – Jeroen Mostert Sep 12 '22 at 14:38
  • Unfortunate. Defaulting to System Admin is a little strange. – Coldchain9 Sep 12 '22 at 14:40
  • The usual workaround for language-specific drivers that are deficient in features is to work through ODBC instead, but `sqlx` doesn't appear to be implementing any ODBC support. There is a dedicated ODBC module, but it will be less convenient to work with. Building ODBC support into `sqlx` seems possible, just not something that anyone has picked up yet. Biting the bullet and just using SQL Server auth (but then not `sa`, obviously) seems like the path of least resistance. – Jeroen Mostert Sep 12 '22 at 14:42

0 Answers0