JDBC connection string with instance name and domain

11
votes

I have a database created in SQL Server with the following configuration:

Also, I am trying to connect to the database named EDS.

For that I created the following details:

Should i have domain in the string or should it be in the username? When i go to the database properties i see the owner has. Also, is the url correct on the instance part according to the image? it looks like it is not able to go to that particular instance.

Also, this is the configuration for the connection (using Windows Authenticaton)

When I put in the username I am getting the following error log:

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user '. ClientConnectionId:e812971f-b03c-4210-9dbd-de0791bcc304

2
sql-serverjdbc
If you want to connect using the instance name then you should not include a port number because the SQL Browser service will figure that out for you. Also, chances are good that a SQL Express instance is not listening on port 1433. (By default, SQL Express instances listen on an arbitrary port number up in the 40000+ range.) - Gord Thompson
I configured the SQLEXPRESS01 to listen on that port. So i should remove the instance on the connection string and use only the port? makes sense @GordThompson - JoaoFilipeClementeMartins
If you've configured for port 1433 yourself then yes, just host name and port number should be all you need. - Gord Thompson
If you want to log into the SQL Server with your Windows credentials then you would add ;integratedSecurity=true to your connection URL and not bother passing a username and password. - Gord Thompson
@GordThompson i am getting that the driver does not support such authentication but i am using sqljdbc42 which is the latest.. - JoaoFilipeClementeMartins

2 Answers

27
votes

When specifying the location of the SQL Server instance, one normally provides serverName\instanceName or serverName:portNumber, not both. That is, either

jdbc:sqlserver://INNOWAVE-99\SQLEXPRESS01;databaseName=EDS

or

jdbc:sqlserver://localhost:1433;databaseName=EDS

(assuming that the SQLEXPRESS01 instance has been explicitly configured to listen on port 1433, which is not usually the case for a SQL Express instance).

As mentioned in the documentation for Building the Connection URL

If both a portNumber and instanceName are used, the portNumber will take precedence and the instanceName will be ignored.

There is no domain= property defined for the connection URL for Microsoft's JDBC driver for SQL Server. Logging in to the SQL Server instance with Windows domain credentials is done implicitly using the integratedSecurity=true connection property (and not explicitly providing a username and password); details here.

0
votes

I'm also having the same problem. Working for me too without port number. Don't forget to give semicolon at the end of the connection.

Connect to the default database on the local computer by using integrated authentication:

jdbc:sqlserver://localhost;integratedSecurity=true;

Connect to a named database on a remote server:

jdbc:sqlserver://localhost;databaseName=AdventureWorks;integratedSecurity=true;

Connect on the default port to the remote server:

jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks;integratedSecurity=true;

Connect by specifying a customized application name:

jdbc:sqlserver://localhost;databaseName=AdventureWorks;integratedSecurity=true;applicationName=MyApp;

Reference Link : mssql document