File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

No access to SQL Express 2005/2008 database

 
Terence Gronowski
Ranch Hand
Posts: 64
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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".





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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Where did you look for "SQL Server and Windows Authentication"? I think SQL Express does support this.
 
Tom Reilly
Rancher
Posts: 618
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 64
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
    Bartender
    Posts: 10336
    Eclipse IDE Hibernate Java
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Does that table exist in the schema for the user you connect as?
     
    Terence Gronowski
    Ranch Hand
    Posts: 64
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Ranch Hand
    Posts: 64
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Bartender
    Posts: 10336
    Eclipse IDE Hibernate Java
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    You need to either add a synonym or put the user in the same schema.
     
    Terence Gronowski
    Ranch Hand
    Posts: 64
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Bartender
    Posts: 10336
    Eclipse IDE Hibernate Java
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator

    ResultSet columns are indexed from 1.
     
    Terence Gronowski
    Ranch Hand
    Posts: 64
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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:



    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
    Bartender
    Posts: 10336
    Eclipse IDE Hibernate Java
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Ranch Hand
    Posts: 64
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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



    I did not check yet, but I hope transfer of data from MySQL to SQL2008 is easier.
     
    Terence Gronowski
    Ranch Hand
    Posts: 64
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Bartender
    Posts: 10336
    Eclipse IDE Hibernate Java
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic