<@U06TM7M40RK> Getting this error
Failed to configure the source (mssql-odbc): (pyodbc.InterfaceError) (‘28000’, “[28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user ‘xxxxx’. (18456) (SQLDriverConnect)”)
(Background on this error at: https://sqlalche.me/e/14/rvf5)
Logging in with user works fine in mssql on windows, but doing a cli ingestion returns this error.
The error message you’re encountering indicates a login failure for the specified user when attempting to connect to the SQL Server using the ODBC driver. This issue can arise due to several reasons, even if the login works fine directly on Windows. Here are some steps to troubleshoot and resolve the issue:
Verify Credentials: Double-check that the username and password specified in your ingestion configuration are correct. Ensure there are no typos or incorrect characters.
Check SQL Server Authentication Mode: Ensure that your SQL Server is configured to allow SQL Server authentication. Sometimes, SQL Server is set to Windows Authentication mode only, which would prevent SQL logins from working.
ODBC Driver Version: Make sure that the ODBC Driver 18 for SQL Server is correctly installed and configured on the system where the CLI ingestion is being run. You can verify the installation by listing the available ODBC drivers using the command:
odbcinst -q -d
Connection String: Ensure that your connection string in the ingestion configuration is correctly formatted. Here is an example configuration for the mssql-odbc source type:
source:
type: mssql-odbc
config:
host_port: 'your_host:1433'
database: 'your_database'
username: 'your_username'
password: 'your_password'
use_odbc: 'True'
uri_args:
driver: 'ODBC Driver 18 for SQL Server'
Encrypt: 'yes'
TrustServerCertificate: 'Yes'
ssl: 'True'
Firewall and Network Configuration: Ensure that there are no firewall rules or network configurations blocking the connection from the machine where the CLI is being run to the SQL Server.
SQL Server Logs: Check the SQL Server logs for more detailed error messages. The logs might provide additional context on why the login is failing.
Would you like more detailed guidance on any of these steps?
Sources:
0 button 
0 button 
Hint: Mention @runllm in the thread for followups.<@U06TM7M40RK> more on Check the SQL Server logs
The DataHub’s documentation I have ingested does not mention how to specifically check SQL Server logs for login failures. To assist further, you might want to provide details on the SQL Server version you are using and the environment setup. If this doesn’t help, please reach out to the DataHub support team for further assistance. 0 button 0 button Hint: Mention @runllm in the thread for followups.
<@U06TM7M40RK> am connecting to an azure sql server
When connecting to an Azure SQL Server and encountering login issues, checking the SQL Server logs can provide valuable insights. However, Azure SQL Database does not provide direct access to the SQL Server error logs like on-premises SQL Server. Instead, you can use the following methods to diagnose login failures:
Azure SQL Database Auditing:
Azure Monitor Logs:
AzureDiagnostics
| where ResourceType == "SQLDatabase"
| where Category == "SQLSecurityAuditEvents"
| where action_name_s == "FAILED_DATABASE_AUTHENTICATION"
SQL Server Management Studio (SSMS):
Azure Portal:
Connection String and Configuration:
Encrypt and TrustServerCertificate settings are correctly configured in your ODBC connection string.If you need more detailed guidance on any of these steps, feel free to ask!
Sources:
0 button 0 button Hint: Mention @runllm in the thread for followups.<@U06TM7M40RK> now getting this error message
'Failed to list jobs due to error (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL ’
“Server][SQL Server]Reference to database and/or server name in ‘msdb.dbo.sysjobs’ is not supported in this version of "
'SQL Server. (40515) (SQLExecDirectW)”)\
The error message you’re encountering indicates that cross-database queries are not supported in Azure SQL Database. This is a common issue when trying to access system databases like msdb in Azure SQL, which are typically available in on-premises SQL Server instances.
Here are some steps to resolve this issue:
Remove References to msdb: Since Azure SQL Database does not support the msdb database, you should remove any references to it in your ingestion configuration or SQL queries. This includes removing any attempts to access msdb.dbo.sysjobs or similar system tables.
Use Azure SQL-Compatible Queries: Ensure that your queries and configurations are compatible with Azure SQL Database. Avoid using features or system tables that are specific to on-premises SQL Server.
Update Ingestion Recipe: If you are using a DataHub ingestion recipe, ensure that it is configured for Azure SQL compatibility. You might need to switch to using the mssql-odbc source type for better compatibility with Azure SQL, as recommended in the <Slack discussion on Azure SQL Ingestion>.
Example configuration:
source:
type: mssql-odbc
config:
host_port: 'your-azure-sql-server.database.windows.net:1433'
database: 'your-database-name'
username: 'your-username'
password: 'your-password'
use_odbc: 'True'
uri_args:
driver: 'ODBC Driver 18 for SQL Server'
Encrypt: 'yes'
TrustServerCertificate: 'yes'
ssl: 'True'
Check for Required Privileges: Ensure that the user account you are using has the necessary privileges on the Azure SQL Database. You may need to grant specific permissions directly on the database you are working with, as Azure SQL does not support cross-database permissions.
If you need more detailed guidance on any of these steps, feel free to ask!
Sources:
0 button 0 button Hint: Mention @runllm in the thread for followups.<@U06TM7M40RK> expound on grant specific permissions directly on the database
To grant specific permissions directly on an Azure SQL Database, you need to ensure that the user has the necessary privileges to perform the required operations. Since Azure SQL does not support cross-database queries or access to system databases like msdb, you must focus on granting permissions within the specific database you are working with.
Here are the steps to grant specific permissions directly on an Azure SQL Database:
Connect to the Azure SQL Database: Use a tool like SQL Server Management Studio (SSMS) or Azure Data Studio to connect to your Azure SQL Database.
Create or Verify User: Ensure that the user you want to grant permissions to exists in the database. If not, create a new user.
-- Create a new user for an existing login
CREATE USER [your_user] FOR LOGIN [your_login];
Grant Necessary Permissions: Depending on the operations you need to perform, grant the appropriate permissions to the user. Common permissions include SELECT, INSERT, UPDATE, DELETE, and VIEW DEFINITION.
-- Grant SELECT permission on all tables
GRANT SELECT ON SCHEMA::dbo TO [your_user];
-- Grant VIEW DEFINITION permission to allow viewing metadata
GRANT VIEW DEFINITION TO [your_user];
Grant Role Memberships: You can also add the user to predefined database roles that have specific permissions.
-- Add user to the db_datareader role for read access
ALTER ROLE db_datareader ADD MEMBER [your_user];
-- Add user to the db_datawriter role for write access
ALTER ROLE db_datawriter ADD MEMBER [your_user];
Verify Permissions: After granting permissions, verify that the user can perform the required operations by testing the connection and executing queries.
These steps ensure that the user has the necessary permissions within the specific Azure SQL Database, avoiding issues related to cross-database access.
Would you like more detailed steps or additional examples?
Sources:
0 button 0 button Hint: Mention @runllm in the thread for followups.Hi 
! Did this interaction resolve your issue or do you still require assistance ?
Please tag @ RunLLM if you still need further assistance !
Or tag @Datahub Community Support if you require a person to take a look
Hope this helps ! ><