Forum Stats

  • 3,751,479 Users
  • 2,250,366 Discussions
  • 7,867,435 Comments

Discussions

MSSQL Connection Issue

3128173
3128173 Member Posts: 17

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

Tagged:

Best Answer

Answers

  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,343 Employee
    edited Sep 9, 2019 11:20AM

    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

  • 3128173
    3128173 Member Posts: 17
    edited Sep 10, 2019 4:09AM

    Thanks for your reply,

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

    pastedImage_0.png

    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

    pastedImage_1.png

    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

    pastedImage_2.png

    Thanks

  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,343 Employee
    edited Sep 10, 2019 7:18AM Accepted Answer

    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

  • 3128173
    3128173 Member Posts: 17
    edited Sep 10, 2019 8:38AM

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

    Thank you