Skip to content
By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post
About re:Post

Connecting AWS Q Business to Microsoft SQL Server hosted on EC2

0

Hi. I have been facing issues in sync-ing Q Business with SQL Server on EC2. I have read the official guide but I couldn't make it work. The error shown is this: JDB-5501 Error: Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

I have allowed TCP connection Sql Server Configuration Manager and created user credentials to be stored in Secret manager. The EC2 is in VPC and it is in public subnet with public ip address.

According to the guide, I have to identify three details: Host, Instance and Port. The host should be the public ip of EC2 right? Instance being the name of server and host obviously 1433. Or did I get any of the details wrong?

It could be the security group and firewall causing the issue. I am also unsure which ip to allow from security group for the port 1433. Thank you.

Edit: I resolved JDB-5501, encountered another error "We couldn't sync all files in the following data source" and managed to find why. Posted answer myself.

  • Adeleke Adebowale .J. EXPERT
    a year ago

    You could do the below checks :- Host: Should be the public IP address (or Elastic IP) of the EC2 instance. Port: Ensure 1433 is configured and open on Security Group and Windows Firewall. Instance: Use the default instance (IP:1433) or specify the named instance (IP\InstanceName,1433). Security Group: Allow traffic from the Q Business IP on port 1433. Firewall: Ensure the Windows firewall on EC2 allows inbound connections on port 1433. Test: Verify the connection with SSMS or telnet to ensure the port is accessible.

  • DanDan
    a year ago

    HI. I have checked all. I had those already enabled. I thought perhaps peer to peer connection could solve the issue as EC2 instance and AWS Q Business are located in different region. Sync failed again but with different error: "We couldn't sync all files in the following data source" The current Q Business IP is vpc ip in my setup.

Topics
Machine Learning & AIBusiness ApplicationsDatabaseAWS Well-Architected Framework
Tags
Microsoft SQL ServerAmazon Q BusinessSecurity
Language
English
asked a year ago167 views
2 Answers
0
Accepted Answer

It was actually the version of SQL server. I overlooked that Q Business only supports SQL Server 2019 when I was using SQL Server 2022. Should have read docs carefully: https://docs.aws.amazon.com/amazonq/latest/qbusiness-ug/ms-sql-server-overview.html

answered a year ago
0

Hello @Daniel,

To connect AWS QuickSight (AWS Q Business) to a Microsoft SQL Server hosted on an EC2 instance, follow these steps:

Step 1: Ensure Network Connectivity Check Security Groups: Ensure the security group associated with your EC2 instance allows inbound traffic on the port used by SQL Server (default is 1433). VPC Configuration: Verify that the EC2 instance is in a VPC that allows communication with AWS QuickSight. This might involve setting up a VPC peering connection or ensuring the instance is in the same VPC.

Step 2: Configure SQL Server Enable TCP/IP Protocol: Use SQL Server Configuration Manager to ensure the TCP/IP protocol is enabled for SQL Server. Set SQL Server Authentication: Ensure SQL Server is configured to allow SQL Server authentication (not just Windows authentication).

Step 3: Set Up IAM Role for QuickSight Create IAM Role: Create an IAM role with the necessary permissions to access the EC2 instance and any other AWS resources needed. Attach Role to QuickSight: Attach this IAM role to your QuickSight account under the "Manage QuickSight" settings.

Step 4: Create a Data Source in QuickSight Open QuickSight: Log in to your AWS QuickSight account. Add New Data Source: Go to "Manage Data" and click "New Data Source." Select SQL Server: Choose "SQL Server" as the data source type. Enter Connection Details: Provide the necessary connection details, including the EC2 instance's public DNS or IP address, port number, database name, and authentication credentials.

Step 5: Test the Connection Test Connection: Use the "Test Connection" feature in QuickSight to ensure that the connection to the SQL Server is successful. Troubleshoot if Needed: If the connection fails, revisit the previous steps to ensure all configurations are correct.

Final Answer By following these steps, you can successfully connect AWS QuickSight to a Microsoft SQL Server hosted on an EC2 instance, enabling you to visualize and analyze your data effectively.

If the Answer is helpful, please click Accept Answer & UPVOTE, this can be beneficial to other community members.

answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Relevant content