[Serco] Got a problem with a CMIS SQL connection

[Oaktech]

I've made a new DB in SQL for our studio school, which seems Ok in itself, but i think i'm missing something in SQL security config for ODBC connections...

As an admin, I can go through, create a new DSN with the following credetials

name - studio,
description - studio
Server - CMIS

This is all as per our original cmis connection.

After clicking next I select

with SQL using a username and password supplied

and enter

our general username studio-general which as far as I can tell is set up exactly the same as our original cmis gneral username
and it's password

I can then click next, and check the database name. click next again, and next again, perform the functional checks and all is well.

If I then log out as an admin and log in as the ordinary user who needs to use it I get an error...

the error says...

connection failed
SQL State: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'OURCOLLEGEDOMAIN\teststaff'


If you click OK you get a 'trusted connection' box which allows you to enter the SQL username and password again which then lets you through.

If at the point of choosing authentication type i choose windows intgrated the result is the same provided I click sql, then enter the username and password and then click back to windows auth before clicking next. If i just click next without entering a username and password it will not proceed


Help.

[BaccyNet]

Is all this on the SQL server or are parts in Facility Admin? The DSN for all our Facility connections here are stored in DSN/CDB files so to point Admin at a different database than your live one we would load up a different CDB file.

Sorry if I've not understood - it's been a hectic day!

[michael2k6]

I'm struggling to get my head around that, it's been a long day, but it's trying to connect with windows authentication at some point, which it shouldn't be.

[bigdannyb79]

Are you sure the CDB you are loading when you are logging in a user is pointing to the new DSN you have created ? Sounds strange to me - as Michael said its using windows authentication which suggests its not picking up the DSN you configured ?

[Oaktech]

Indeed - it confused the living hell out of me for ages, but i've fixed it now...

What i'd done, was create the CDB file on the server, which automatically selected a trusted connection, which only a domain admin can provide. We want the cdb to be stored on the server, but what i needed to do was to create a cdb as an ordinary user, thus not using a trusted connection, and then move the cdb to the server before pointing the shortcut to it.

Thanks for your input everyone, it was head scratcher for a while, but i think i'm good now!

[michael2k6]

I've never known that happen, server or otherwise, it was a head scratcher you're right. Normally the CDB file is kept in the client files folder and not shared. The reason being that it stores the last saves academic year, the last place a backup was taken to, the last place a timetable extraction was taken to. So if you've got people changing academic years for timetabling etc and saving as they exit there's a good chance someone's going to open it the following morning in the wrong year and do an hours worth of work before they realise.

Mic

[Oaktech]

I've never known that happen, server or otherwise, it was a head scratcher you're right. Normally the CDB file is kept in the client files folder and not shared. The reason being that it stores the last saves academic year, the last place a backup was taken to, the last place a timetable extraction was taken to. So if you've got people changing academic years for timetabling etc and saving as they exit there's a good chance someone's going to open it the following morning in the wrong year and do an hours worth of work before they realise.

Mic

There are 4 users using this particular instance of the database, they all have their own copies of the CDB file and the files but as they are multi-desk users the install roams with their profiles, the users are in a group which runs a targeted startup script which copies the files, including the CDB from a directory called admin-username on the server creating the admin folder to the machine in question and sets the dsn, When the user logs off, the dsn is unset and the files are copied back to the network location. Every week a batch file copies the users admin folder to a backup location, which in turn is included in the weekly site backup.