Friday, February 24, 2012

jdbc refuses connection with ''user not associated with a trusted SQL Server connection'' me

When using jdbc with IntegratedSecurity, I run into this problem when the machine is not part of a domain & gets its IP address via dhcp. Is this expected behavior or a bug in the jdbc driver.

The SQLServer and client application are installed on the same machine and a local admin is logged in, running the client app.

If I change one of the two parameters mentioned above, the connection can be established leading me to believe this may be intentional for security issues. Am I correct?

To clarify, "not part of domain" means you disconnected from the domain or you machine is part of workgroup? Do you mean that "IP address works but server name dosen't or otherwise around.|||That is correct. If I have the scenario where the machine is part of a workgroup and I am using dhcp, the connection gets refused. If I either add the machine to a domain or assign a static IP address to the NIC), the connection succeeds. So, what I meant by 'using dhcp' is that the NIC is configured to use dhcp in the TCP/IP settings of the Network Connection properties. I have not tried an environment where one NIC is dhcp and another is static.

Also, if I join a domain, the connection will succeed with the NIC configured for dhcp.

Hopefully that is clearer.

|||

To help us understand the problem and narrow down where the problem might be, please provide us with the SQL Server version and the JDBC driver version?

Please also provide the JDBC connection URL (minus any sensitive data).

Can you verify that another driver (say SNAC / MDAC) properly connects to SQL Server in the same configuration to make sure it is a JDBC driver issue versus some machine configuration issue.

If your SQL Server is SQL Server 2005, you can find the "sqlcmd.exe" tool in the "Microsoft SQL Server\90\Tools\Binn\" install directory. You can connect with Integrated Authentication to the local SQL Server using sqlcmd.exe with the following syntax: sqlcmd -Hlocalhost -E

Alternatively, if you have "osql.exe" you can perform the same test "osql -Hlocalhost -E". If you do not have access to either of these tools, you can also use a "Universal Data Link" file. Please see http://msdn2.microsoft.com/en-us/library/e38h511e(vs.71).aspx and http://support.microsoft.com/kb/274536 for additional information.

This will help narrow down if there is an issue with machine configuration or JDBC driver.

Jimmy

|||SQL Version: SQL Server 2005 Express Edition (tried both SP1 and SP2)
JDBC Version: 1.1

Since I am using SQL Server 2005, I did run both sqlcmd and osql and was able to execute the following statement

SELECT convert(varchar(15), SERVERPROPERTY('productversion')), convert(varchar(5), SERVERPROPERTY('productlevel')), convert(varchar(20), SERVERPROPERTY('edition'))

I got a row back with the version information I expected so it looks like I was able to connect. In addition, SQL Server Management Studio is able to run and connect with Windows Authentication.

Here is the code that I am trying to execute. The intention of this snippet it to connect to SQL Server so that I can create a database. The user running the application had admin rights.

Code Snippet

try
(
String address = "1.1.1.1";
String port = "1433";
String db_name = "MyAppDB";
Connection my_connection;
String url;
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
url = "jdbc:sqlserver://" + address + ":" + port + ";integratedSecurity=true;";
my_connection = DriverManager.getConnection(url);

Statement create_stmt = conn.createStatement();
create_stmt.execute("CREATE DATABASE " + db_name);
}
catch (SQLException e)
{
//WriteErrorMsg("Error establishing JDBC connection (" + e.getErrorCode() + ") " + e.getMessage());
//return OPERATION_ERROR;
}
catch (ClassNotFoundException e)
{
//WriteErrorMsg("Class error creating " + db_name + ": " + e.getMessage());
//return EXCEPTION_CAUGHT;
}

Let me know if there is any other info I can get you.
Thanks for the help
john
|||

In the code snippet provided you are attempting to connect to an IP address of 1.1.1.1 and port 1433. If your machine has DHCP enabled, does it actually get 1.1.1.1 as the IP address?

If you only plan to run the client application on the same machine as SQL Server, then I would recommend either specifying "localhost" as the server name or the actual server name in the connection URL.

example: url = "jdbcTongue Tiedqlserver://localhost:1433;integratedSecurity=true;"

Also, typically SQL Server Express is not installed to use the fixed port 1433, did you configure the instance to the fixed 1433 port?

If not, I would recommend enabling the SQL Browser service and update the connection URL to something like:

url = "jdbcTongue Tiedqlserver://localhost;instanceName=SQLEXPRESS;integratedSecurity=true;"

HTH,

Jimmy

|||That was not the real address. The snippet is part of function where the address is passed in as a paramenter. It would either be set to an address(ie 10.47.20.46) or localhost. I just put that in as more of placeholder. Bad choice of documenting, sorry for any confusion. The SQL server may or may not be on the same machine. In the case where SQL is local on another machine, I would not use integrated security and provide credentials for SQL authentication.

Yes, SQL is configured to configured for port 1433 in this case.

thanks

No comments:

Post a Comment