Cloud Pak for Data

 View Only

  • 1.  Making a connection to SQL Server from AWS Cloud

    Posted Wed October 28, 2020 11:03 PM
    Hi ,

    I am a beginner for IBM Cloud pak for data.I am testing how to retrieve data for a project using a data connection.
    For that I am using a AWS cloud instance. Within that I created SQL server express data base.

    Then I have the IBM Cloud pak for data account by browsing within the AWS Instance.

    I tried to enter the parameters for data connection and failed to make the connection. Retrieve a message as follows.

    "The connection cannot be created.

    The platform could not connect to the Microsoft SQL Server data source.Ensure that the connection details and credentials are correct. If the problem persists, there might be an issue with the network or the data source"

    For HostName or IP Address I used the value I am getting from the query run on that particular db instance 'SELECT @@SERVERNAME'
    For port number I used 1433 the default number.
    username(sa) and password are the values from SQL authentication password.

    Can someone guide where I have gone wrong. Appreciate for the support.


    ------------------------------
    Suresh Abeyweera
    ------------------------------

    #CloudPakforDataGroup


  • 2.  RE: Making a connection to SQL Server from AWS Cloud

    Posted Fri October 30, 2020 01:27 PM
    1. Do you have a DBA to ask to confirm that SQL Server is up and running and that the "sa" account is enabled?
    2. Have you tried connecting to the SQL Server using the SQL credentials in another application?  (e.g. MS Excel, MS Access, etc.)  If other applications are successful then SQL Server is probably fine.  If they cannot connect, the error messages can be helpful.  

    Recommendations (a few off top of my head):
    • Ensure that the SQL Server service is running. 
    • Verify the SQL server edition. (If it is SQL Server Express, then additional configuration steps are needed. These can be searched on the internet. )
    • Verify the SQL instance.  Typically the default instance is good to go; a named instance should be too.  However, will need SQL Browser running for named instances.  
    • Verify the SQL Server Configuration; especially the network configuration.  May need to specify port 1433 for TCP/IP connections for that instance. 
    • Recommend creating another SQL login with appropriate permissions.  The "sa" account is typically disabled. 
    • Oh yeah, verify that SQL login is mix mode.  By default, SQL Server is installed with Window authentication only.  


    ------------------------------
    Doug Stevens
    ------------------------------

    Original Message


  • 3.  RE: Making a connection to SQL Server from AWS Cloud

    Posted Fri October 30, 2020 01:27 PM
    Things to try: 
    1.  If you have a DBA; ask for their help with SQL Server connections. 
    2.  Try connecting to SQL Server from another application; such as MS Excel or MS Access.  If these cannot connect then its probably a SQL server configuration. If these can connect then the focus might be spent on Cloud Pak.

    Recommendations (a few off top of my head):
    • Verify that the SQL Server service are running.
    • Ensure that mixed mode authentication is turned on in SQL Server.  By default its only Windows Authentication. 
    • Encourage creating another SQL login other than "sa"; this account is typically turned off by default.
    • Verify the edition of SQL Server.  If its SQL Server Express, then other configuration steps are needed (these can be done by a DBA; and can be easily searched on the internet.)
    • Verify the network protocols are enabled and configured properly in SQL Server Configuration (these can be done by a DBA; and can be easily searched on the internet.)
    • If the SQL Server instance is a named instance, then SQL Server Browser service should be running. 


    ------------------------------
    Doug Stevens
    ------------------------------

    Original Message


  • 4.  RE: Making a connection to SQL Server from AWS Cloud

    Posted Wed November 04, 2020 07:52 AM
    Things to try: 
    1.  If you have a DBA; ask for their help with SQL Server connections. 
    2.  Try connecting to SQL Server from another application; such as MS Excel or MS Access.  If these cannot connect then its probably a SQL server configuration. If these can connect then the focus might be spent on Cloud Pak.

    Ideas to check if SQL Server Connection is not working (a few off top of my head):
    • Verify that the SQL Server service are running.
    • Ensure that mixed mode authentication is turned on in SQL Server.  By default its only Windows Authentication. 
    • Encourage creating another SQL login other than "sa"; this account is typically turned off by default.
    • Verify the edition of SQL Server.  If its SQL Server Express, then other configuration steps are needed (these can be done by a DBA; and can be easily searched on the internet.)
    • If the SQL Server instance is a named instance, then SQL Server Browser service should be running. This is sometimes disabled. 
    • Verify SQL Server Configuration's network protocols; e.g. TCP/IP is enabled and port 1433 is specified. 


    ------------------------------
    Doug Stevens
    ------------------------------

    Original Message


  • 5.  RE: Making a connection to SQL Server from AWS Cloud

    Posted Wed November 04, 2020 07:52 AM
    Things to try: 
    1.  If you have a DBA; ask for their help with SQL Server connections. 
    2.  Try connecting to SQL Server from another application; such as MS Excel or MS Access.  If these cannot connect then its probably a SQL server configuration. If these can connect then the focus might be spent on Cloud Pak.

    Recommendations (a few off top of my head):
    • Verify that the SQL Server service are running.
    • Ensure that mixed mode authentication is turned on in SQL Server.  By default its only Windows Authentication. 
    • Encourage creating another SQL login other than "sa"; this account is typically turned off by default.
    • Verify the edition of SQL Server.  If its SQL Server Express, then other configuration steps are needed (these can be done by a DBA; and can be easily searched on the internet.)
    • Verify that the appropriate network protocols are enabled and configured in SQL Server Configuration.  (This can be searched easily on the internet.)
    • If the SQL Server instance is a named instance, then SQL Server Browser service should be running. 


    ------------------------------
    Doug Stevens
    ------------------------------

    Original Message


  • 6.  RE: Making a connection to SQL Server from AWS Cloud

    Posted Wed November 04, 2020 07:52 AM
    Assuming this is an EC2 instance, the value returned by SELECT @@SERVERNAME would be part of the Private IPv4 DNS of the instance. This would not be something that we could connect to directly. Instead you should use either the Public IPv4 address or the Public IPv4 DNS value, from the  EC2 summary page, as the hostname.

    ------------------------------
    Brian Tinnel
    ------------------------------

    Original Message


  • 7.  RE: Making a connection to SQL Server from AWS Cloud

    Posted Wed November 04, 2020 07:53 AM

    When you say you created an AWS cloud instance, what do you mean exactly. 

    If you are creating an EC2 instance then:

    • For HostName, you need to use either the Public IPv4 IP Address or the Public IPv4 DNS name from the Instance summary page.  The value returned by SELECT @@SERVERNAME is the Private IPv4 DNS name and only usable inside on the internal AWS network.
    • Make sure that port 1433 is open in the security group used by your instance.  Depending on how you created the instance, by default, only port 22 is open.



    ------------------------------
    Brian Tinnel
    ------------------------------

    Original Message