.NET Core supports integratedsecurity on Unix and MacOS via Kerberos through GSSAPI. It seems to all be open source under an MIT license now, so an implementation could be based on .NET Core. 😄

Hello. I am using tiberius on some database ORM, and I am unable to play with float types.

Whenever I define a classical float on SqlServer, tiberius always broke the result on the row.get() method.

Is this a know bug? Whatching the crate tiberius-derive, they specifically talk about this one.

Known issues:

There is a tiberius bug(?) that breaks Option fields at the moment. Tiberius appears to always parse null Float(53) (double) columns as Nullable(f32) independently of the column type.

So, my Rust field is an Option. If I try to change it to an Option...

Result::unwrap()` on an `Err` value: Conversion("cannot interpret F32(None) as an f64 value")

Is this a known bug? Exists some workaround over it? How can I fix my code? I am using it on an enterprise level application, releasing the code in production, and we're getting delayed because we can't work with our float columns.

Thanks in advice for your time.

Hello!

I am trying out Tiberius with integrated authentication on RHEL. Connection is failing with below error

Error: Token error: ' 'Logon failed for login 'DOMAIN\USERNAME' due to trigger execution.' on server SERVERNAME executing  on line 1 (code: 17892, state: 1, class: 14)

Could someone help?

fixes prisma/tiberius#197

Next to trust_cert another option is now available to instead add another root-certificate in pem or der format. The new option is also supported as ado-/jdbc-connection-strings.

Certificate generation scripts have been added to generate a self-signing ca and a server certificate. The server certificate and ca is added to the two MSSQL instances in Docker-Compose.

this is my code

let mut config = Config::from_ado_string("server=xxx.database.windows.net,1433;UID=xxx;PWD=xxx;TrustServerCertificate=true")?;
let tcp = TcpStream::connect(config.get_addr()).await?;
tcp.set_nodelay(true)?;
let mut client = Client::connect(config, tcp.compat_write()).await.expect("Failed!");

and I get thread 'main' panicked at 'Failed!: Tls("connection closed via error")', src/main.rs:80:22

Running INSERT INTO table VALUES(...) is really slow especially when e.g. inserting against an Azure server. You can batch inserts with INSERT INTO table VALUES(({},{}),({},{})), but that is limited to 1000 values. You can also use INSERT INTO table SELECT a,b FROM (VALUES {},{},{},{}))sub(a,b) as mentioned in https://github.com/steffengy/tiberius/issues/93, but you are limited to 2100 values in the query.

A solution to this would be to implement something similar to what Tedious (NodeJS library) implements: https://tediousjs.github.io/tedious/bulk-load.html

Things to do until hitting the publish button for the async/await version of Tiberius. Started from a state where the transportation and quite a many basic types already work and are usable.

• [x] date/datetimeoffset/datetime2/smalldatetime/datetime/time types
• [x] text/ntext types
• [x] binary/varbinary
• [x] image
• [x] xml
• [x] User-defined types
• [ ] Transaction system. Maybe the connection should finally be Arc<Mutex<Connection>> in the Client, so a transaction can roll back on Drop if needed. (https://github.com/prisma/tiberius/issues/28)
• [x] Guid type
• [x] Execute doesn't really need to be a stream, The results should be collected to a vector for better error handling and provided as an iterator or using the total method.
• [x] ADO.NET connection strings
• [x] Testing SSPI windows login (on windows)
• [x] Windows authentication
• [x] Testing SQL browser mode (on windows)
• [x] Testing TLS
• [x] Testing Azure
• [ ] Builder and connection should handle all possible features and options.
• [x] AppVeyor setup for this fork.
• [x] Work on tokio, async-std and smol (https://github.com/prisma/tiberius/issues/29)
• [x] Fix numeric handling (https://github.com/prisma/tiberius/issues/31)

As mentioned in #176, tiberius currently does not provide the option to register a custom CA certificate. When the server is running with a self-signed cert, currently the only option is to trust any certificate, which basically disables the trust completely.

The tiberius config should be enhanced with an option to register a custom CA which is then passed to the underlying ssl implementation.

tiberius by default use OpenSSL. In one of our projects, we hit reliability issues with OpenSSL and found rustls provides a better option for TLS.

To unblock myself, I create a fork to use rustls. Initially I want to add a feature to I could later merge back. But I found I have to change some interfaces from future::AsyncRead to tokio::AsyncRead. This makes the code really dirty. So I give adding as a feature and just adapt the code to tokio::AsyncRead.

I would love to merge back the change. But not sure what's the best option here. What's the suggestion?

It is fantastic to see new progress in tiberius. I had tried to use a much earlier version but no support of blob types was a show stopper.

I have tried using the sample code from the documentation, both against the tokio and the async-std executors. My database is Microsoft SQL Server 2016 (SP2-CU8) (KB4505830) - 13.0.5426.0 (X64) .

A simple SELECT query worked fine with both executors, however when I tried another query which retrieved a single row with two columns, row index (integer key column) and corresponding BLOB (Image type), from another table the result is corrupted:

let mut stream = client.simple_query(_query2).await?;
assert!(stream.next_resultset());
println!("{:?}",stream.columns().unwrap());

returns the name of the index and BLOB column

[Column { name: "i", column_type: Int4 }, Column { name: "琀攀猀琀搀愀琀愀开戀氀漀戀", column_type: Image }]

The types are correct for both columns but the column name for the BLOB column should be 'b'. It is corrupted. Also if I continue on and try extract the rows I get a "Error: Protocol error: invalid token type 0".

I am not overriding the default tiberius features and get the same results with the latest stable rust on both Windows and Ubuntu 64bit. Any suggestions for how to debug this?

I tried running the same query with freetds "tsql" and it returned the correct data.

• Allow users to choose either async-naitve-tls or async-tls to provide tls
• Add testing for rustls to CI (appveyor only for now)
• Would be interesting to see if this can fix #65

PR: https://github.com/prisma/tiberius/pull/266

A deliberately setup bulk insert results into error during finalize() call:

Error: Io { kind: BrokenPipe, message: "An error occurred during the attempt of performing I/O: An existing connection was forcibly closed by the remote host. (os error 10054)" }

SQL Server logs show:

Error: 4014, Severity: 20, State: 17.

This is fully reproducible and appears to be related to packet sizing: increasing/decreasing number of rows by 1 from 1481 results in a successful insert, as changing first column name to C1....

Tracing shows that indeed last packet size is over 4096 bytes:

...
2022-12-25T22:02:35.688309Z TRACE tiberius::tds::codec::bulk_load: Bulk insert packet (4096 bytes)
2022-12-25T22:02:35.690583Z TRACE tiberius::tds::codec::bulk_load: Bulk insert packet (4096 bytes)
2022-12-25T22:02:35.696377Z TRACE tiberius::tds::codec::bulk_load: Finalizing a bulk insert (4107 bytes)

Repro code with tiberius = { version = "0.11.3", features = ["chrono", "time", "tds73"] }:

use std::error::Error;
use async_std::net::TcpStream;
use tiberius::{Client, Config, TokenRow, ToSql};

#[async_std::main]
async fn main() -> Result<(), Box<dyn Error>> {
    let connect_string = "server=tcp:localhost,1433;user=SA;password=<YourStrong@Passw0rd>;TrustServerCertificate=true;";

    let config = Config::from_ado_string(connect_string)?;
    let tcp = TcpStream::connect(&config.get_addr()).await?;
    tcp.set_nodelay(true)?;
    let mut client = Client::connect(config, tcp).await?;

    let create_table = r#"
        CREATE TABLE ##TestBulkInsert
        (
            MetricResultID INT NULL,
            ExperimentID INT NOT NULL,
            MetricID int NOT NULL,
            VariantNumber TINYINT NOT NULL,
            ResultDate DATETIME2(3) NOT NULL,
            ResultValue FLOAT NULL,
            ResultLowerBound FLOAT NULL,
            ResultUpperBound FLOAT NULL,
            ResultConfidence FLOAT NULL,
            ResultIsSignificant BIT NULL,
            MetricAnalysisLogID INT NULL,
            InsertDate DATETIME2(3) NULL,
            ControlDenominator FLOAT NULL,
            ControlNumerator FLOAT NULL,
            VariantDenominator FLOAT NULL,
            VariantNumerator FLOAT NULL,
        )
    "#;
    
    client.simple_query(create_table).await?;
    
    let mut insert = client.bulk_insert("##TestBulkInsert").await?;
    
    let dt = chrono::Utc::now().naive_utc();

    for _ in 0..1481 {
        let mut row = TokenRow::with_capacity(16);
        row.push(0i32.to_sql());
        row.push(0i32.to_sql());
        row.push(0i32.to_sql());
        row.push(0u8.to_sql());
        row.push(dt.to_sql());
        row.push(0f64.to_sql());
        row.push(0f64.to_sql());
        row.push(0f64.to_sql());
        row.push(0f64.to_sql());
        row.push(false.to_sql());
        row.push(0i32.to_sql());
        row.push(dt.to_sql());
        row.push(0f64.to_sql());
        row.push(0f64.to_sql());
        row.push(0f64.to_sql());
        row.push(0f64.to_sql());
        insert.send(row).await?;
    }

    insert.finalize().await?;

    Ok(())
}

Hello. Following this issue about conversion errors on floating point types, I found also the same error for the tiberius tiberius::ColumnType::Intn.

An easy to read example:

TIB_TY::Intn => Self::IntValue(
    row.get_opt::<i32>(col.name())
),

My row type is just a trait object, that wraps your row.get<T, U>() row extractor. I am extracting data by a string reference &str as my QueryIdx parameter.

Fix is as easy as the fix applied on the floating point types issues, but for the Intn.

Is it possible to have a fix for this issue?

Thanks.

Hello. Following this issue a lot of people are interested in make the QueryIdx trait public.

By make it public, you will allow to your consumers to write generic code that can abstract the get() or try_get() implementations far beyond the actual implementations, that is monomorphize with some &str or usize those methods over Row.

Would be possible to make a public reexport of the QueryIdx trait?

Problem

As described in the referenced issue, the way tiberius converts between DateTime<FixedOffset> and DateTimeOffset is incorrect. Currently, tiberius uses the DateTime<FixedOffset>'s local datetime + offset to create the DateTimeOffset. This means that what is stored in the database will be a local datetime and its offset.

However, as described in the docs:

"The data is stored in the database and processed, compared, sorted, and indexed in the server as in UTC. The time zone offset will be preserved in the database for retrieval."

the value should be stored in the database as a UTC datetime + offset.

The consequence of tiberius's current behavior is that retrieving DateTimeOffset values, that have been inserted by tiberius, using any other methods or languages will yield the wrong value. Likewise, using tiberius to retrieve values that have been inserted by other methods or languages will also yield the wrong value.

Changes

• Changes the conversion between DateTime<FixedOffset> and DateTimeOffset to use UTC + offset instead of local + offset.
• Adds a test that demonstrates the problem, which fails without these changes.

Closes https://github.com/prisma/tiberius/issues/260

While inserting and retrieving DateTimeOffset values from a SQL Server database, I noticed a surprising behavior where the inserted value is stored differently in the database than what was inserted and retrieved.

Setup

main.rs

use tiberius::{
    time::chrono::{DateTime, FixedOffset},
    AuthMethod, Client, Config,
};
use tokio::net::TcpStream;
use tokio_util::compat::TokioAsyncWriteCompatExt;

#[tokio::main]
async fn main() {
    let mut config = Config::new();

    config.host("0.0.0.0");
    config.port(1433);
    config.authentication(AuthMethod::sql_server("sa", "super_secret_password12"));

    config.trust_cert();

    let tcp = TcpStream::connect(config.get_addr()).await.unwrap();
    tcp.set_nodelay(true).unwrap();

    let mut client = Client::connect(config, tcp.compat_write()).await.unwrap();

    let _ = client
        .execute(
            r#"
DROP TABLE Test
           "#,
            &[],
        )
        .await;

    client
        .execute(
            r#"
CREATE TABLE
    Test (
        id BIGINT IDENTITY(1, 1) PRIMARY KEY,
        field DATETIMEOFFSET NOT NULL
    )
           "#,
            &[],
        )
        .await
        .unwrap();

    let d = "2022-05-20T11:30:11.642+02:00"
        .parse::<DateTime<FixedOffset>>()
        .unwrap();

    dbg!(&d);

    client
        .execute(
            r#"
INSERT INTO
    Test (field)
VALUES
    (@P1);
           "#,
            &[&d],
        )
        .await
        .unwrap();

    client
        .query(
            r#"
SELECT
    *
FROM
    Test
           "#,
            &[],
        )
        .await
        .unwrap()
        .into_row()
        .await
        .unwrap()
        .map(|r| {
            let d: DateTime<FixedOffset> = r.get("field").unwrap();
            dbg!(d)
        });
}

Cargo.toml

[package]
name = "tiberius-test"
version = "0.1.0"
edition = "2021"

[dependencies]
chrono = "0.4.23"
tiberius = { version = "0.11.3", features = ["chrono"] }
tokio = { version = "1.22.0", features = ["full"] }
tokio-util = { version = "0.7.4", features = ["compat"] }

docker-compose.yml

version: "3.8"

services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: mssql
    environment:
      SA_PASSWORD: super_secret_password12
      ACCEPT_EULA: Y
    ports:
      - 1433:1433

Execution

$ docker-compose up -d
$ cargo run

    Finished dev [unoptimized + debuginfo] target(s) in 0.03s
     Running `target/debug/tiberius-test`
[src/main.rs:50] &d = 2022-05-20T11:30:11.642+02:00
[src/main.rs:82] d = 2022-05-20T11:30:11.642+02:00

$ sqlcmd -S "localhost,1433" -C -U sa -P super_secret_password12
1> select * from Test;
2> go
id                   field
-------------------- ---------------------------------------------
                   1            2022-05-20 13:30:11.6420000 +02:00

(1 rows affected)

Result

As shown, the inserted and retrieved DateTimeOffset values are the same while using tiberius. However, when looking at the actual value stored in the database using something like sqlcmd there is a different value. The inserted value was 2022-05-20T11:30:11.642+02:00, but the database shows the value to be 2022-05-20 13:30:11.6420000 +02:00. This means that if you interact with the database using something other than tiberius you would get the wrong value.

Is this the intended outcome, or a bug?

Hey there! Thanks so much for this awesome crate!

I want to write a custom wrapper for try_get, like this:

fn get_c_opt(r: &Row, id: impl QueryIdx) -> Result<Option<String>> {
    Ok(r.try_get::<&str, _>(id)?.map(|s| s.to_string()))
}

fn get_c_or_empty(r: &Row, id: impl QueryIdx) -> Result<String> {
    Ok(get_c_opt(r, id)?.unwrap_or_default())
}

But the QueryIdx trait is not exposed, so this doesn't compile. Is there any way to work around this, or could we expose the trait?