Failed connecting to Azure Sql database

[farazfaheem]

Hi,
I have Azure SQL database that I am trying to DB migrate the scripts. I am implying windows authentication in connection string, but it does not seem to accept the connection string.

I'm using the below connection string
Server=tcp:xxx.windows.net,1433;Initial Catalog=xxx;Persist Security Info=False;User ID=xx@xx.com;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Integrated";

Below is the error I am getting

c:\> yuniql list --platform sqlserver --debug                                                                                                                

Running yuniql v1.1.55 for windows-x64
Copyright 2019 (C) Rodel E. Dagumampan. Apache License v2.0
Visit https://yuniql.io for documentation & more samples

ERR   2021-03-10 09:44:01Z   Failed to execute list function. Keyword not supported: 'authentication'.
Diagnostics stack trace captured a System.ArgumentException:
   at System.Data.SqlClient.SqlConnectionStringBuilder.GetIndex(String keyword)
   at System.Data.SqlClient.SqlConnectionStringBuilder.set_Item(String keyword, Object value)
   at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
   at System.Data.SqlClient.SqlConnectionStringBuilder..ctor(String connectionString)
   at Yuniql.SqlServer.SqlServerDataService.GetConnectionInfo() in C:\projects\yuniql\yuniql-platforms\sqlserver\SqlServerDataService.cs:line 49
   at Yuniql.Core.MetadataService.GetPreparedSqlStatement(String sqlStatement, String metaSchemaName, String metaTableName) in C:\projects\yuniql\yuniql-core\MetadataService.cs:line 34
   at Yuniql.Core.MetadataService.GetAllVersions(String metaSchemaName, String metaTableName, Nullable`1 commandTimeout) in C:\projects\yuniql\yuniql-core\MetadataService.cs:line 216
   at Yuniql.Core.MigrationService.GetAllVersions(String metaSchemaName, String metaTableName) in C:\projects\yuniql\yuniql-core\MigrationService.Utils.cs:line 49
   at Yuniql.CLI.CommandLineService.RunListOption(ListOption opts) in C:\projects\yuniql\yuniql-cli\CommandLineService.cs:line 159
If you think this is a bug, please report an issue here https://github.com/rdagumampan/yuniql/issues.

Thank you.

[rdagumampan]

Hi @farazfaheem, sorry for delayed response, been busy lately on python and databricks :). I have investigated this and it seems related to the SqlClient I used. The issue could be fixed if I use Microsoft.Data.SqlClient. It can also be fixed if I dont use the SqlClient.SqlConnectionStringBuilder.

I dont have a fixed yet but as work around for now would be to use Sql Account. Please stay tune, I will pickup all these open bugs during my summer holiday in Mid May :)

[rdagumampan]

@farazfaheem, with the latest upgrade of sql driver to Microsoft.Data.SqlClient on #224, the keyword seems to have been valid now. I tested old builds in the same connection string and old throws the error you encounted and the latest build from master, recognized that the target server is inaccessible. When you have chance, try the Nightly Build. This fix will be part of v1.3 due for release in Q3 2022.

Thanks for your contribution and good luck!

SETX YUNIQL_PLATFORM "sqlserver"
SETX YUNIQL_WORKSPACE "C:\play\yuniql\samples\basic-sqlserver-sample"
SETX YUNIQL_CONNECTION_STRING "Server=tcp:xxx.windows.net,1433;Initial Catalog=xxx;Persist Security Info=False;User ID=xx@xx.com;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Integrated;"


INF   2022-05-08 12:54:08Z   Run configuration:
{
  "workspace": "C:\\play\\yuniql\\samples\\basic-sqlserver-sample",
  "platform": "sqlserver",
  "connectionString": "Server=tcp:xxx.windows.net,1433;Initial Catalog=xxx;Persist Security Info=False;User ID=xx@xx.com;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Integrated;",
...
}
...
ERR   2022-05-08 12:54:24Z   Failed to execute run function. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)
Diagnostics stack trace captured a Microsoft.Data.SqlClient.SqlException (0x80131904):
 ---> System.ComponentModel.Win32Exception (11001): No such host is known.