JDBC connection to MSSQL server in windows authentication mode

Question

In my following programme I am trying to connect MSSQL Server using jdbc in windows authentication  But getting following error  import java io    import java sql    import java util GregorianCalendar  import java util Date  import java text DateFormat  import java text SimpleDateFormat  import java util Calendar   class  Cms truncate       public static void main String   args                  Calendar cal   new GregorianCalendar                String name  cmscim              String filename    D   programs  Tomcat 6 0  webapps  timescape  canteen scheduller  CMS CSV  cms cim     name  -  cal get Calendar YEAR    -    cal get Calendar MONTH  1     -  cal get Calendar DATE    csv            Connection conn   null           String url    jdbc sqlserver   localhost 1433 databasename CMS TIMES MAIN integratedSecurity true            String driver    com microsoft sqlserver jdbc SQLServerDriver            String userName                 String password                Statement stmt           try                      Class forName driver     newInstance             conn   DriverManager getConnection url userName password            String query    select   from cim where sapId 10025331            stmt   conn createStatement             int flag   stmt executeUpdate query            System out println  flag     flag             conn close            System out println                 catch  Exception e             e printStackTrace                          I am using SQL Server in windows authentication mode  Do I need to do set up other things to connect MSSQL using jdbc in windows authentication   ERROR   com microsoft sqlserver jdbc SQLServerException  The TCP IP connection to the host localhost  port 1433 has failed  Error   Connection refused  connect  Verify the connection properties  Make sure that an instance of SQL Server is running on the host and accepting TCP IP connections at the port  Make sure that TCP connections to the port are not blocked by a firewall        at com microsoft sqlserver jdbc SQLServerException makeFromDriverError SQLServerException java 190      at com microsoft sqlserver jdbc SQLServerException ConvertConnectExceptionToSQLServerException SQLServerException java 241      at com microsoft sqlserver jdbc SocketFinder findSocket IOBuffer java 2243      at com microsoft sqlserver jdbc TDSChannel open IOBuffer java 491      at com microsoft sqlserver jdbc SQLServerConnection connectHelper SQLServerConnection java 1309      at com microsoft sqlserver jdbc SQLServerConnection login SQLServerConnection java 991      at com microsoft sqlserver jdbc SQLServerConnection connect SQLServerConnection java 827      at com microsoft sqlserver jdbc SQLServerDriver connect SQLServerDriver java 1012      at java sql DriverManager getConnection Unknown Source      at java sql DriverManager getConnection Unknown Source      at Cms truncate main Cms truncate java 28

User · Answer

After struggling a lot  I finally found a solution  here we go -  Download the file jtds-1 3 1 jar and ntlmauth dll and save it in Program File -  Java -  JDK -  jre -  bin    Then use the following code -   String pPSSDBDriverName    com microsoft sqlserver jdbc SQLServerDriver   Class forName pPSSDBDriverName   DriverManager registerDriver new com microsoft sqlserver jdbc SQLServerDriver     conn   DriverManager getConnection  jdbc jtds sqlserver    lt ur server port gt  UseNTLMv2 true Domain AD Trusted Connection yes    stmt   conn createStatement    String sql     DELETE FROM  lt data gt  where  lt condition gt   stmt executeUpdate sql

User · Answer

From your exception trace  it looks like there is multiple possibility for this problem   1   You have to check that your port  1433  is blocked by firewall or not  If you find that it is blocked then you should have to write  Inbound Rule   It if found in control panel -  windows firewall -  Advance Setting  Option found at Left hand side  -  Inbound Rule   2   In SQL Server configuration Manager  your TCP IP protocol will find in disable mode  So  you should have to enable it

User · Answer

If you want to do windows authentication  use the latest MS-JDBC driver and follow the instructions here   https   msdn microsoft com en-us library gg558122 v sql 110  aspx

User · Answer

Nop  you have a connection error  please check your IP server adress or your firewall  com microsoft sqlserver jdbc SQLServerException  The TCP IP connection to the host localhost  port 1433 has failed  Error   quot Connection refused  connect  Verify the connection properties  Make sure that an instance of SQL Server is running on the host and accepting TCP IP connections at the port  Make sure that TCP connections to the port are not blocked by a firewall  quot    ping yourhost  maybe the ping service was blocked  but try anyway   telnet yourhost 1433  maybe blocked   Contact the sysadmin with the results

User · Answer

i was getting error as  quot This driver is not configured for integrated authentication quot  while authenticating windows users by following jdbc string  jdbc sqlserver   host 1433 integratedSecurity true domain myDomain  So the updated connection string to make it work is as below   jdbc sqlserver   host 1433 authenticationScheme NTLM integratedSecurity true domain myDomain  note  username entered was without domain

User · Answer

You need to add sqljdbc auth dll in your C  windows System32 folder  You can download it from http   www microsoft com en-us download details aspx displaylang en amp id 11774

User · Answer

Try following these steps    Add the integratedSecurity true to JDBC URL like this   Url  jdbc sqlserver    lt  lt Server gt  gt   lt  lt Port gt  gt  databasename  lt  lt DatabaseName gt  gt  integratedsecurity true   Make sure to add the sqljdbc driver 4 or above version  sqljdbc jar  in your project build path   java sql DatabaseMetaData metaData   connection getMetaData    System out println  Driver version     metaData getDriverVersion      Add the VM argument for your project    Find the sqljdbc auth dll file from DB installed server  C  Program Files sqljdbc 4 0 enu auth x86   or download from this link  Place the dll file in your project folder and specify the VM argument like this  VM Argument  -Djava library path   lt  lt DLL File path till folder gt  gt    NOTE  Check your java version 32 64 bit then add 32 64 bit version dll file accordingly

User · Answer

Using windows authentication   String url   jdbc sqlserver   PC01 inst01 databaseName DB01 integratedSecurity true     Using SQL authentication    String url   jdbc sqlserver   PC01 inst01 databaseName DB01

User · Answer

For the current MS SQL JDBC driver  6 4 0  tested under Windows 7 from within DataGrip    as per documentation on authenticationScheme use fully qualified domain name as host e g  server your domain not just server  the documentation also mentions the possibility to specify serverSpn MSSQLSvc fqdn port REALM  but I can not provide you with details on how to use this  When specifying a fqdn as host the spn is auto-generated  set authenticationScheme JavaKerberos set integratedSecurity true use your unqualified user-name  and password  to log in   As this is using JavaKerberos I would appreciate feedback on whether or not this works from outside Windows  I believe that no  dll is needed  but as I used DataGrip to create the connection I am uncertain  I would also appreciate Feedback on this

User · Answer

You need to enable the SQL Server TCP IP Protocol in Sql Server Configuration Manager app  You can see the protocol in SQL Server Network Configuration

[java] JDBC connection to MSSQL server in windows authentication mode

Examples related to java

Examples related to sql-server-2005

Examples related to jdbc