Azure Question posted in
The official documentation to get you started can be found here.

Error While Connecting to SQL Server via Azure Data Factory

I am new to ADF, I am trying to connect with SQL Server through SSMS setup from my local system. For this, I have created a keyvault in Azure and stored username and passsword into that. Also I have setup Azure self hosted integration run time. However, when I try to create a connection in ADF for Azure Key vault it is through below error:

enter image description here

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Issue: As already mentioned above the error, I have created a user/login in SSMS(SQL Server) and trying to connect it with ADF to read from SQL Server database. All permissions are already granted to that user also I have created the access policy so it can read from key vault using my ADF instance. Still I was facing error.

    Solution:

    1. I was connecting with SQL server authentication in my ADF, however my SSMS instance was default set to windows authentication. I clicked on the server->properties->security then switch from Windows Authentication Mode to SQL Server and Windows Authentication mode

    2. Then I expand security folder, here right click on my login->properties->status, here login should be enabled

    3. Then closed the SSMS instance, and open SQL Server Configuration Manager to restart SQL Server Service. Once restarted, then use SQL Server Authentication mode to connect and it was successful. Verfied it from connecting it with my ADF instance as well.


  2. 0 
    Cannot connect to SQL Database. Please contact SQL server team for further support. Server: '****', Database: '****', User: '****'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.
Login failed for user '****'., SqlErrorNumber=18456,Class=14,State=1,

    The error indicates that Login failed for user. There is an issue with the user credential. Ensure that you have entered the correct credential, and verify that the user has the necessary permissions to access the mentioned database. If the user lacks permissions, you may encounter login issues like the one above.

    By connecting to the same database you are attempting to connect to in ADF through SSMS or Azure Data Studio with the same credentials, you can check the user’s permissions. Alternatively, execute the command below to find the user’s access to the database:

    SELECT HAS_DBACCESS('<databaseName>');

    HAS_DBACCESS returns 1 if the user has access to the database, 0 if the user has no access, and NULL if the database name is not valid.

    Using the correct credentials and granting the required user permissions to access the database will allow you to connect to the database in ADF successfully, as shown below:

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search