wood burning stoves 2.0*
The moose likes JDBC and the fly likes No access to SQL Express 2005/2008 database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "No access to SQL Express 2005/2008 database" Watch "No access to SQL Express 2005/2008 database" New topic
Author

No access to SQL Express 2005/2008 database

Terence Gronowski
Ranch Hand

Joined: Dec 19, 2007
Posts: 64
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


Docendo discimus (we learn by teaching)
SCJP 5 Zertifizierung, Vorbereitungsbuch; SCJP 6 Zertifizierung, Vorbereitungsbuch

Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Tom Reilly
Rancher

Joined: Jun 01, 2010
Posts: 618
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

Joined: Dec 19, 2007
Posts: 64
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

    Joined: Apr 14, 2004
    Posts: 10336

    Does that table exist in the schema for the user you connect as?
    Terence Gronowski
    Ranch Hand

    Joined: Dec 19, 2007
    Posts: 64
    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

    Joined: Dec 19, 2007
    Posts: 64
    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

    Joined: Apr 14, 2004
    Posts: 10336

    You need to either add a synonym or put the user in the same schema.
    Terence Gronowski
    Ranch Hand

    Joined: Dec 19, 2007
    Posts: 64
    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

    Joined: Apr 14, 2004
    Posts: 10336


    ResultSet columns are indexed from 1.
    Terence Gronowski
    Ranch Hand

    Joined: Dec 19, 2007
    Posts: 64
    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

    Joined: Apr 14, 2004
    Posts: 10336

    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

    Joined: Dec 19, 2007
    Posts: 64
    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

    Joined: Dec 19, 2007
    Posts: 64
    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

    Joined: Apr 14, 2004
    Posts: 10336

    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.
     
    wood burning stoves
     
    subject: No access to SQL Express 2005/2008 database
     
    Similar Threads
    Bug in JDBC ? Simple Select statement not returning a resultSet
    problem in connecting sql 2005
    Servlets and SQL Express 2005
    SQL Server DB connection
    SQL server weird connection problem-Pls help