No access to SQL Express 2005/2008 database

[Terence Gronowski]

I want to link a SQL Server Expess 2005 Database to a simple Java application. The resulting error is:

Exception: Fehler bei der Anmeldung für den Benutzer 'terencewin'. Der Benutzer
ist keiner vertrauenswürdigen SQL Server-Verbindung zugeordnet. (means: The user is not associated with a trusted SQL Server connection)

Microsoft reccommends: Change the Authentication Mode of the SQL server from "Windows Authentication Mode (Windows Authentication)"
to "Mixed Mode (Windows Authentication and SQL Server Authentication)". But in the Express versions there is no Mixed Mode. Either can I find some security tab to change the secuity authentication mode to "SQL Server and Windows".

The database is called ppverwaltung and the table person. The database can be accessed by Microsoft SQL Server Management Studio Express.

I use sqlcjbc4.jar as driver in a correct classpath statement.

The user "terencewin" is a SQL-Server-Authentication user with the password "test".

import java.sql.*;

public class TestDB {
  Connection con;
  Statement st;
  ResultSet rs;
  public TestDB(String query){
    try{

        String url = "jdbc:sqlserver://localhost:1433;databaseName=ppverwaltung;user=terencewin;password=test;";
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        //DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
        con = DriverManager.getConnection(url);
        System.out.println("nach DriverManager");
        st = con.createStatement();
        st.executeQuery(query);
        rs = st.getResultSet();

       while ( rs.next() ){
        System.out.println(rs.getString(0));
        System.out.println(rs.getString(1));
        System.out.println(rs.getString(2));
        System.out.println(rs.getString(3));
      }
    }
    catch(Exception e){
       e.printStackTrace();
            System.out.println("Exception: " + e.getMessage());
    }
  }
  public static void main(String[] argv){
    TestDB test = new TestDB("SELECT * FROM ppverwaltung.dbo.person;");
  }
}

I alternatively tried to get access by a Windows-Authentication, no luck.

I too tried to eshablish a DataSourceName, but this did not work too.

Somebody knows the error? Help urgently needed

[Paul Sturrock]

Where did you look for "SQL Server and Windows Authentication"? I think SQL Express does support this.

[Tom Reilly]

The documentation says you should

MSDN wrote:Use the Authentication Mode page of the Microsoft SQL Server Installation Wizard to choose the form of authentication used to validate connections to this instance of SQL Server Express.

[Terence Gronowski]

I just found the Mixed mode in Express-Version:

Start > Programs > Microsoft SQL Server 2005 > SQL Server Management Studio

Right-click the Server name

select Properties > Security

Server Authentication, select "SQL Server and Windows Authentication Mode"

restart server

Microsoft really knows howto hide important features very well!!!

But now I have the following error: Ungültiger Objektname 'person'. (it does not find the table "person").

[Paul Sturrock]

Does that table exist in the schema for the user you connect as?

[Terence Gronowski]

I am not quite sure

When I look at the "Anmeldungseigenschaften>Benutzerzuordnung" ("Login properties>user association" ??) there appears a table with the associations:

Database=ppverwaltung, User=terence, Standardschema=dbo

[Terence Gronowski]

The table is in the group dbo, I probably have to put the user "terence" to the group/schema dbo, anybody knows how?

[Paul Sturrock]

You need to either add a synonym or put the user in the same schema.

[Terence Gronowski]

I put user "terence" to schema "dbo" by "Eigenschaften der Tabelle - parkplatz" (means properties of the table "parkplatz2).

But, now, I get the next error:

Der Index "0" liegt ausserhalb des gültigen Bereichs. (the inex "0" lies bejond the valid range)

Hell, there must be an index with a value of 0!

[Paul Sturrock]

 System.out.println(rs.getString(0));  

ResultSet columns are indexed from 1.

[Terence Gronowski]

Paul many thanks, that's it!!

I just put the correct code here again for clarity:

Database: ppverwaltung
Table: parkplatz

parkplatz has to be acessed by the schema "dbo" eg. dbo.parkplatz

Here the working code:

import java.sql.*;

public class TestDB {
  Connection con;
  Statement st;
  ResultSet rs;
  public TestDB(String query){
    try{

        String url = "jdbc:sqlserver://localhost:1433;databaseName=ppverwaltung;user=terence;password=test;";
        
        //String url = "jdbc:sqlserver://localhost:1433;databaseName=ppverwaltung;user=NT-AUTORITÄT\\SYSTEM;";
        
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        //DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
        con = DriverManager.getConnection(url);
        System.out.println("nach DriverManager");
        st = con.createStatement();
        st.executeQuery(query);
        rs = st.getResultSet();

       while ( rs.next() ){
        System.out.println(rs.getString(1)); //!!!! not 0!!!!
        System.out.println(rs.getString(2));
        System.out.println(rs.getString(3));
        System.out.println(rs.getString(4));
      }
    }
    catch(Exception e){
       e.printStackTrace();
            System.out.println("Exception: " + e.getMessage());
    }
  }
  public static void main(String[] argv){
    TestDB test = new TestDB("SELECT * FROM dbo.parkplatz;");
  }
}

To resume:

type in the port 1433 manually in the TCP/IP properties

change the server authentication from Windows-Authentificationmode to SQL Server- and Windows Authentification

put the user "terence" to the schema (group) dbo

rs.getString(0) is not allowed!!!

That was a lot to do

Thanks a lot for the help

[Paul Sturrock]

You are welcome.

Things worth considering (or knowing) as you go forward:

You can use Windows Authentication only. Some drivers (jTDS and the latest MS one) support NTLM

Accessing fields by their index is risky, unless you also know for sure the query will always return the same fields in the same order

You should always close connections as soon as you are finished with them in a finally block

[Terence Gronowski]

Still some remarks to SQL 2005 and 2008 Server Express:

I first tryed with SQL 2005 Server as the installation process is shorter. Now I see that the SQL of 2005 is very limited, so that it is not possible to import a SQL-Dump from MySQL.

Despite the more steps to install 2008 Server Express it is recommended, as (hopefully) the syntax is closer to the syntax of the actual MySQL version.

To install SQL 2008 Server you have to do the following:

Install:
NetFramework 3.5
Installer 4.5
PowerShell
SQL 2008 Express Server
Management Console 2008
sqljdbc4.jar (JDBC-Driver, put into classpath)

Configuration:

Configure Port (eg. 1433)
Change authentication to Windows and SQL-Server
Create user in management console
add sqljdbc4.jar to classpath


The code

//Liest aus SQL Server 2008
import java.sql.*;
public class SqlServer{
  public static void main(String[] args){
         int id2=1;
         Connection con = null;
         String nachname;
         String vorname;
         String persnr;
         String autonr;
         String frei;
         String gpsx;
         String gpsy;

     try {

         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
            "databaseName=ppverwaltung;user=teri;password=test;";
         con = DriverManager.getConnection(connectionUrl);

         Statement st = con.createStatement();

         String sql="select  * from dbo.person";
         System.out.println(sql);
         ResultSet rs = st.executeQuery(sql);    //Speichern executeUpdate()
         while(rs.next()) {
           nachname = rs.getString(2);
           vorname = rs.getString(3);
           persnr = rs.getString(4);
           autonr = rs.getString(5);

           System.out.println("nachname " + rs.getString(2));
           System.out.println("id " + rs.getString(1));
           }
         } catch(Exception e) {
             e.printStackTrace();
            System.out.println("Exception: " + e.getMessage());
       } finally {
         try {
           if(con != null)
           con.close();
         } catch(SQLException e) {}
       }
  }
}

I did not check yet, but I hope transfer of data from MySQL to SQL2008 is easier.

[Terence Gronowski]

Still a remark:

Sql Server Express 2008 works well on a XP 32 bit System.

I first installed Sql Server Express 2005 and then Sql Server Express 2008 without deinstalling the 2005 version on a XP 64. This gave a mess. I just recommend to deinstall an old Express version before installing a new one. I'm not sure, if the errors came because of the 64 bit XP.

[Paul Sturrock]

The installation instructions for SQL Server itself (the server component anyway) state you need to uninstall previous versions first. I believe you can have multiple versions of the client tools. If you need multiple versions of the server, run them in a VM.