Forum Discussion

Emranit's avatar
Emranit
Helper II
1 year ago
Solved

Power BI unable to connect with SQL database server using an encrypted connection.

When a power BI connected with SQL database server with only server IP address, it's connected without database name. But not showing my expected table "Dyelots" in the list. But this table "Dyelots"...
Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    Hi Emranit ,

    Thanks for reaching out to the Microsoft fabric community forum. 

     

    Please follow below steps:

    1. Encrypted Connection Issue in Power BI:
    Power BI by default tries to use encrypted connections. If your SQL Server doesn't support SSL/TLS encryption (or uses a self-signed certificate), the connection fails. That’s why you get the "encryption support" error.

    2. Database Not Fully Accessible via Server IP Only
    When you don’t specify the database name, Power BI connects to the default database for that user. If "Dyelots" is in a different DB, or under a different schema, it won't show.

    3. Table Visibility Issue
    Sometimes Power BI doesn't show all tables due to:

    Permissions (user login doesn't have rights to see certain tables). The table being under a non-default schema. Table being a temporary or system object


    Please follow the below steps to Fix It:

    1. Disable Encryption in Power BI (Manually)
    Since there is no visible checkbox in the Power BI UI for encryption:

    Go to Advanced Options in the SQL Server connection screen. In the SQL Server Name field, add this at the end of your server name:

    ;Encrypt=False

    Example:

    192.168.57.83;Encrypt=False
    Also, ensure that the database name is still entered in the Database field. Leave the SQL statement blank unless needed.

    2. Verify SQL Permissions
    Make sure the SQL login you're using in Power BI:

    Has read access to the “Dyelots” table. Is connected to the correct database. Use this query in SSMS while logged in with the same credentials as Power BI:


    SELECT * FROM ORGATEX-INTEG.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Dyelots';

    3. Try DirectQuery Mode Temporarily
    If Import mode fails, try selecting DirectQuery to see if that changes how it retrieves table metadata.

    4. Use a SQL Query
    In the Advanced Options of Power BI, instead of navigating the table list, you can directly write a SQL query like:

    SELECT * FROM Dyelots

    This often bypasses metadata issues.

     

    Please check community forum threads.

    Solved: unable to connect to the data source using an encr... - Microsoft Fabric Community

    Solved: Unable to connect to the data source using an encr... - Microsoft Fabric Community

     

     

    If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
    Thanks and Regards

     

46's avatar
46
Regular Visitor
1 year ago

Hi Emranit,
1.  Fix the Encrypted Connection Issue in Power BI

Power BI by default tries to enforce an encrypted connection to SQL Server. If your server doesn’t support it (e.g., self-hosted, local instance), it fails.

 

Solution:

Use Advanced Options in Power BI’s SQL Server connector and disable encryption.

 

Steps:

 

  1. In Power BI Desktop, click: Home > Get Data > SQL Server
  2. In the dialog:
    • Server: 123.123.123.123 (your actual IP address)
    • Database: YourDatabaseName
    • Click Advanced Options
      • Check: Encrypt connection → ❌ Uncheck this
      • Optional: Add SQL query like SELECT * FROM Dyelots (to verify)
  3. Authentication:
    • Choose Database or Windows (depending on your SQL Server setup)
    • Enter credentials

👉 Now try connecting — it should succeed and show your tables.

 

📌 3. Ensure the Table Appears (like Dyelots)

If Power BI connects but you don’t see "Dyelots", it could be because of:

  • Permissions: Ensure the user you’re using in Power BI has SELECT rights on that table.
  • Schema mismatch: Power BI only shows objects the user has access to. Check the schema in SSMS:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Dyelots'

Confirm the schema (e.g., dbo.Dyelots) and that your login has permission.

🧰 4. Optional: Use Direct SQL Query

If you're still not seeing the table in Navigator, do this:

  1. In Power BI > SQL Server connector
  2. Click Advanced Options
  3. Paste this query:

SELECT * FROM dbo.Dyelots

  1. Uncheck Encrypt connection
  2. Connect

This directly pulls your expected table even if it’s not listed in the navigator UI.

💡 5. Optional: Enable unencrypted connections on SQL Server (server-side fix)

If you're managing the server, you can enable unencrypted connections or install a certificate to support encrypted ones.

In SQL Server Configuration Manager:

  • Go to SQL Server Network Configuration > Protocols for MSSQLSERVER
  • Right-click TCP/IP > Properties > Flags tab
  • Set Force Encryption = No

Then restart SQL Server service.

Good luck with your troubleshooting, and feel free to update this thread with your progress!

 

Once the issue is resolved, kindly mark this solution as ACCEPTED if it helped you, so others with a similar problem can find it more easily. Thanks!