4 Replies Latest reply on Sep 10, 2019 12:38 PM by 3128173

    MSSQL Connection Issue

    3128173

      Hi All,

       

      I am trying to create a connection to  an MS SQL Server 2017 database in Data Dictionary with poor results.

      I have tried it both in Data Modeler 18.4 and 19.1 following the steps below:

      1. Added under the third party JDBC drivers  mssql-jdbc-7.0.0.jre8.jar
      2. Created a new JDBC MSSQL connection under data dictionary using the following settings:
        • Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
        • JDBC URL:three different urls were used
          • v1: jdbc:sqlserver://localhost;port=1434;integratedsecurity=true;
          • v2: jdbc:sqlserver://localhost\bam_db;instance=mssqlserver01;port=1434;integratedSecurity=true
          • v3:jdbc:sqlserver://localhost\mssqlserver:1434;integratedsecurity=true;database=bam_db

                The first two urls (v1 &2) are tested successfully, while the third fails with an error message of: "Cannot open database "bam_db" requested by the login. The login failed".

                However, even though the first two urls are tested successfully, if we try to connect to the database,the "Select Schema/Database" screen shows only the schemas of msdb and tempdb and their schemas. In v1 this is expected as we did not specify the database so it took the default one, however in v2 this should not be the case.

       

      Any ideas?

       

      Edit 9/9:

      Repeated the above steps in Data modeler 9.2 with jdbc7.4.

      After downloading jdbc 7.4 it was added under "Third party jdbc drivers", and sqljdbc_auth.dll was copyied to thr bin and lib folders under the jre path.

      After adding a connection using the options of v3 above i received again the same message : "Cannot open database "bam_db" requested by the login. The login failed".

       

      Has anyone managed to create a connection to MS SQL Server 2017 using Datamodeler? This looks like a serious bug.

       

      Thanks

        • 1. Re: MSSQL Connection Issue
          Philip Stoyanov-Oracle

          I don't have MS SQL Server 2017 available but you are not using the right path. You trying to use general JDBC import.  After adding the JDBC driver you'll have MS SQL Server as supported database.

          Here is the right path:

          MS_SQLServer_connection.png

          I have very old JDBC drivers and have no problem to connect to MS SQL Server 2016. You can put some options after the port number like - 1434;integratedSecurity=true

          Philip

          • 2. Re: MSSQL Connection Issue
            3128173

            Thanks for your reply,

            I tried to use the SQL Server database type  with the following options

             

            The port settings used are: 1434;integratedSecurity=true;instance=mssqlserver01

            I also tried including the database name, but in both cases testing the connection results in the following message: "Status :Failure - Test failed: Login failed for user 'modeler'"

             

            Now, the username / password used  in this attempt are valid, and i confirmed them again, with the only thing that may be worth noting being that the modeler account is an sql account.

             

            Interestingly, the below connection details for this connection available in the left side of the database connection pane, end with /null , and a wild guess would be that this results in an invalid url and subsequently to a login fail.

             

            Am i missing something?

            I use the developer edition of MS SQL server 2017.

             

            Thanks

            Edit 10/9:

             

            After transferring the instance under the host option, testing the connection was successful, however it seems that if i specify the database as in the example below,

             

            Hostname: localhost\mssqlserver01

            Port: 1434;integratedSecurity=true;database=bam

             

            I receive an error message of "Status : Failure - Test failed : cannot open database "Bam" requested by the login. The login failed"

             

            On the other hand if the database name is not referenced, the connection test is successfull, but when actually connecting to the database the only available databases and schemas are the ones under :System databases".

            I changed the default database for modeler to "bam" with the same effect

            Thanks

            • 3. Re: MSSQL Connection Issue
              Philip Stoyanov-Oracle

              I tried with two types of connection and got all databases

              sqlserver_connection.png

              Connection_JdbcUrl.png

              databases_MSMS.png

              databases.png

              You need to find out your settings

              Philip

              • 4. Re: MSSQL Connection Issue
                3128173

                After enabling TCP/IP through configuration manager everything works fine.

                 

                 

                Thank you