August 28, 2012 at 2:09 pm
I'm a botanist by training, not a database manager, but I inherited the role of DM (dungeon master in my case) from someone who did nothing to make my life easier.
Here's my problem (sorry if the terms I'm using aren't the right syntax): We operate a multiuser database that stores all of the data in SQL Server tables, but we use Access as a front-end system for data analysis/data entry. In theory, I should be able to allow access by adding new users (I'm an administrator for all of the servers we use for the database) in the servers and by making the new users part of the group "CTAP Professionals", because that is listed as an approved user in SQL Server Management Studio. Everyone else connects in the same way. The problem is that when I try to make the ODBC connections for new users, I get the following error :"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." We're not using SQL Server authentication, we're set up for Windows Authentication. One of the local IT guys and I were able to make a successful connection for another user, but he can't remember what he did to make it happen. I'd rather not enlist his help (that isn't very helpful), so I'd like to ask if there's something out there that I can try to make this happen.
August 28, 2012 at 2:29 pm
ryetimothy (8/28/2012)
I'm a botanist by training, not a database manager, but I inherited the role of DM (dungeon master in my case) from someone who did nothing to make my life easier.
Please do not take this the wrong way. No sarcasm is intended here.
This has got to be the most awesome post I've ever seen on here...
August 28, 2012 at 2:40 pm
ryetimothy (8/28/2012)
In theory, I should be able to allow access by adding new users (I'm an administrator for all of the servers we use for the database) in the servers and by making the new users part of the group "CTAP Professionals", because that is listed as an approved user in SQL Server Management Studio.
Agreed, but we may need to ask more details about that to make sure there's no missteps, but we'll get back to that.
Everyone else connects in the same way. The problem is that when I try to make the ODBC connections for new users, I get the following error :"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." We're not using SQL Server authentication, we're set up for Windows Authentication.
Alright, first up, when it says 'trusted connection', it's talking about your network login. Trusted = Network. This sounds similar to what's known as a 'double-hop' error. Quick design question...
I'm assuming you have an Access Front End on each person's machine so that they're running reports and the like locally instead of off a single resource, like you would when you RDP somewhere. Does this connect to a shared 'linked table' library access database, or directly to the SQL Server? If it's connecting to the shared database first, it's possible you're double-hopping the authentication which Kerberos isn't trusting anymore.
If that's not it, can you give us a bit more of an explanation about your design architecture, in particular if this is an ADP (Access Development Project, uses the MSDE engine) or if it's a DB with linked ODBC tables?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 28, 2012 at 2:54 pm
I'm assuming you have an Access Front End on each person's machine so that they're running reports and the like locally instead of off a single resource, like you would when you RDP somewhere. Does this connect to a shared 'linked table' library access database, or directly to the SQL Server? If it's connecting to the shared database first, it's possible you're double-hopping the authentication which Kerberos isn't trusting anymore.
If by 'linked table' library access database you mean the tables in the Access side of things show up with the prefix "dbo", then yes, that's how we do it.
If that's not it, can you give us a bit more of an explanation about your design architecture, in particular if this is an ADP (Access Development Project, uses the MSDE engine) or if it's a DB with linked ODBC tables?
This is a DB with linked ODBC tables. In my understanding, all of the tables are stored in SQL Server, but they're linked (over an ODBC connection) to the Access database that everyone uses. If I have to make any changes to the tables I have to do it in SQL Server Management Studio and then refresh the linked table in Access to update the tables.
August 28, 2012 at 2:57 pm
I should add (don't know if this is relevant), but the Access database is on one server (Panda), but the server that I use to access the SQL Server tables in SQL Server Management Studio is different (Tulip).
August 28, 2012 at 3:55 pm
As a troubleshooting test, can you load a copy of the database itself (not the SQL table side, just the access one) onto a user's system and see if their ODBC will work when the database is local?
I'm concerned that it's trying to 'hop' to Panda, use Panda's ODBC to 'hop' to the SQL Server, and you're double-hopping. If it works when local to their machine, then you've got a network authentication issue which is common enough that you'll want to make sure it's not the problem. If it still won't work from the client's machine, then we'll have to look at the ODBC settings itself and make sure it's using trusted connection settings and the like, both on the client and on Panda. I'm afraid I don't remember offhand if a locally launched copy of Access will use the source file's server settings or the local ones.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply