Connection error while connecting to SQL Database

[Former Member]

Hello Everyone,

I am trying to connect to MS SQL server using an IDBC connector.I followed following procedure:

1. Create Data Server-> Choose connector type as IDBC connector-> Finish.

2. Enable the DataServer and allow dynamic query.

3. Go to connection tab. Make the setting as shown in diagram.

Here, I am giving credentials of Database ower. Also, in my server url, <serverName > and <portNo> are mii server name and mii port number. <abc> is the database name.

Pls help me with what I am going wrong.

Regards,

Minakshi.

[Former Member · Accepted solution]

Hello Everyone,

I am resolved with the issue. The proble was not from MII side. The SQL credentials I was using were not assigned proper roles to establish the connection. Now I am able to connect to SQL database.

Thanks for your response.

-Minakshi

[Former Member]

Hi Minakshi,

As per your screenshot, your server URL string is wrong. there "microsoft" is missing.

the string will be :

jdbc:microsoft:sqlserver://<host>:<port>;DatabaseName=<db>;

You can try with both host name or IP, sometime it may cause issue.

As per your JDBC Driver string, you have to use JDBC driver. If it is installed and handled in SQL server then you have no need to put it in MII "JDBC Driver" else you have to upload it.

Another point is, if you still get the status as error, just check user in the MSSQL server, sometime, the user got locked in the data server end due to 3 wrong hit.

Hope it will help you,

Suman

[Former Member]

Hi Suman,

Thans for yourt reply.

I tried adding the microsoft in my url after Rohit's post but error still persisted.

I also tried using IP and found no difference. Also the user is not locked as I am able to login to the server.

And how to check whether JDBC driver is installed and handled in SQL server?

Regards,

Minakshi

[Former Member]

Hi Minakshi,

Check the exception in the logs.

Check these things also:

1. IDBC Driver is uploaded and deployed in the JDBC Drivers screen.

2. In Server url, <serverName > and <portNo> should be of machine where MS Sql is installed.

Also check the following blog if you need to download the drivers:

Regards,

Rohit Negi.

[Former Member]

Hi Rohit,

Thanks for your reply.

<serverName > and <portNo> are of machine where MS Sql is installed, mii was written by mistake.

I am not sure where to look for exceptions in logs.

I have one more MII server where I am trying to connect to SQL database in similar conditions. IDBC driver is not uploaded on that server too, but it works fine there.

Do I still need to install that driver?

Regards,

Minakshi.

[Former Member]

Hi Minakshi,

The JDBC driver which MII has, can only connect to SQL Server 6.X, 7.X or 2000 and the JDBC driver name is 'com.inet.tds.TdsDriver'.

Refer the MII Help Doc: IDBC Connector - SAP Manufacturing Integration and Intelligence - SAP Library

If you have other versions of MSSQL server then this driver might not work and in this case you need to upload the proper compatible driver.

What is the MSSQL server version?

If you use the TDSDriver then these are the params for IDBC Server:

JDBC Driver: com.inet.tds.TdsDriver

Server URL: jdbc:inetdae:<Machine IP>:<Port(by default 1433)>?database=<DB Name>&sql7=true

Validation Query: SELECT GETDATE()

If this does not works then you need to upload the Drivers.

Refer the above mentioned blog link mentioned in the earlier reply.

Also you can check the Logs from MII.

Go to System Management --> Log Viewer

Check the Logs here to see what exceptio or error is coming

Regards,

Rohit Negi.

[Former Member]

Hi Rohit,

I am using MS SQL 2008R2.
As you said, I downloaded the driver. Unzipped the file and found two jar files. So I uploaded them in JDBC drivers screen and then deployed all. But its not helpful. I also noticed that when we create a default connector ie with com.inet.tdsTdsDriver the connector status is "stopped" and when I change it to com.microsoft.sqlserver.jdbc.SQLServerDriver, it goes to "error" state.

Also the Log viewer has no entries so far. Screenshot of logviewer:

My JDBC screen shot for you refrence:

Pls confirm that I am using the right jar file.

Regards,

Minakshi

[Former Member]

Hi Minakshi,

Remove the sqljdbc.jar and only keep sqljdbc4.jar

I am also using the sqljdbc4.jar in our MII system and using the following connection properties:

But my MSSQL server and MII are on the same machine.

So check the following thread:

Also I think the logs are not coming due to authorization problem.

Give proper netweaver roles to your user.

You need to check the exception coming hte logs to figure out what exactly is the problem.

Regards,

Rohit Negi.