I'm hoping someone here has an answer to this, its driving me crazy.

I have a client using an ODBC connection (on XP Pro), to connect to a SQL Server database. Its set to use NT authentication.

You can create the ODBC Datasource, select the appropriate database on the appropriate server and test the connection - all is fine.

However, when you check it is actually connected to the another database on that Server, apparently the one defined as the defualt for that server.

For instance I created a connection pointing at the standard Northwind database, then I opened Access, linked to an ODBC datasource, selected my new datasource and instead of being offered a list of the tables in Northwind, I was offered a list of the tables in the other default database (not master).

I eventually got it to work as I expected, by logging off of the domain account and doing a login as the adminstrator for the local machine. In that account, the DSN worked as expected.

I have never seen a DSN with NT authentication simply provide access to another database - it should tell me I don't have access rights to the database selected or whatever.

In this case the default database is the clients main live production system, so it appears to be a glaring security risk.

Anybody know why this might happen, or even how to make it happen/stop?

Thanks.

well, you did specify the database to connect to in the DSN, right?
if you don't explicitly specify this, it will connect to whatever is the default ...

how are you trying to access the DB? through Enterprise Manager? code?
Andy

Thats the point - I am (of course) specifying the database - in my example I specified Northwind, but when I issued a query, using the ODBC connection I was actually connected to the other database (lets called it 'Company1') - so instead of seeing the tables and data in Northwind, I am seeing the tables and data in Company1. I tried with 4 or 5 databases, but each time it was the tables and data in company1 that I see.

I have never seen this behavior - when I go back into the Data Sources Administrator, it says I'm configured to look at Northwind still - so it remembers my settings etc.

I see this via OLE or more obviously by linking to the database via ODBC in Microsoft Access.

They won't let me use Enterprise Manager becuase of the 'security risk' despite the fact I am pointing out that anybody can get into their main live database and screw with it.

I found a problem on MSDN with similar kind of symptoms but it was for per 9/2000 versions of the drivers, I checked and they have 2004 dated drivers.

I also tried with SQL Server authenitcation, for a new DB user with access privileges to only one database (not company1). I still got the Company1 data!

I assume its an environmental issue, but I don't know what

R

QUOTE (richardL @ Feb 6 2006, 05:09 PM)

I assume its an environmental issue, but I don't know what

mhmmm, i don't no squat about MS Access ...

but all DSNs should work equal. that is odd at best. i assume the transport is set to TCP/IP and not Named Pipes ...

now one thing i can think of, when they installed SQL Server, they might have not installed it to recognize both, NT Security *AND* SQL Security. the installer gives you the option of either or both. i always use both and only use NT for *local* connections through Named Pipes and always SQL for remote connection through TCP/IP ...
i never use NT Security for remote connections. ever ...

never seen this before. but you *should* be able to test this through enterprise manager, even if they don't want you to use it.

oh, another thing, do they have SQL Server run on the default Port (1433)?
sometimes, if you set it up incorrectly, you can end up with two instances running, one on the default port, pointing to the default DB and one at the custom port pointing to the right DB.
i had that happen before ...

Andy

Our ODBC Admin at work says that testing the database doesn't really do squat, in fact it might only test the TCP/IP connection to the server.

And that's all I have to say about that.

QUOTE (richardL @ Feb 6 2006, 04:07 PM)

I have never seen a DSN with NT authentication simply provide access to another database - it should tell me I don't have access rights to the database selected or whatever.

that actually has nothing to do with the DSN setup. if the user account they gave you has no restrictions, you'll be able to use it to access any database on the SQL server.

if the account was only supposed to be used with one specific database but actually works with others as well, the account is set up incorrectly ...


you specify which user can access what database devices (see pic 1) and you also set a "default" database for that user (see pic 2) ...

so, maybe, the useraccount you are using is not configured correctly ...
Andy

PIC 1

PIC 2

hey richard, if the DB server is on a public network, just PM me the info and i'll poke at it a bit ...

Andy

you are using a system DSN right? not a user?

user DSN's are user specific.

QUOTE

I have never seen a DSN with NT authentication simply provide access to another database - it should tell me I don't have access rights to the database selected or whatever.

sure it can as long as there's no security on the DB. Setting up DSN's are a "portal" to a specific location. either local or over a network.

user DSN's are user specific and a pain in the @ss if your setting up a multi user SQL setup.

QUOTE

In this case the default database is the clients main live production system, so it appears to be a glaring security risk.

are you saying that if you login as local admin you can then access the network DB via ODBC???

you've probably already setup a user on the SQL side so to address the NT authentication just go here... network path, then folder, then test connection. that should be it.

Its not a public site - its behind a firewall.

I understand all about setting up users to have access to specific databases etc.

The reason I am confused is that the connection is setup (as a system DSN, there are no User DSNs) to go to a specific database and its telling me that I am connected to that database, however I am actually accessing a different database, to which I have no access rights. Yet I can query and get data from it (I didn't try an insert since it was their Live system )

I am sure that there is an issue with user rights, or the way SQL Server is setup - I just can't find anything that would even allow me to specify such behavior. What I am looking for is a way to 'turn it off' from doing this

And, yes, logging on as a local admin (not a network admin) on that machine cured the problem - so I had admin privilege on the client, but not on SQL Server and it made a difference.

Richard

QUOTE (richardL @ Feb 7 2006, 08:38 AM)

And, yes, logging on as a local admin (not a network admin) on that machine cured the problem - so I had admin privilege on the client, but not on SQL Server and it made a difference.

there lies the problem. when you use NT Security, your LOCAL (or domain) credentials are used to connect to the remote source.
i bet, if you're a local admin you have correct access to the DB but if you're just a simple user, it get's all screwed up ...

as i said in my previous post, i NEVER use NT Security for remote logins, always SQL Login ...

Andy

QUOTE (SirAndy @ Feb 7 2006, 10:07 AM)

there lies the problem. when you use NT Security, your LOCAL (or domain) credentials are used to connect to the remote source.

That might well be part of the problem - although the user certainly has full rights on the original desired database. However, it is NEVER correct behavior, whether the user has rights or not, to actually substitute another database, to which the user DOES NOT have rights - that is absolutely wrong and should never happen.

In this case it means that anybody, regardless of their rights to access any database, can create a DSN and gain access to a restricted database by default - a huge security hole. If someone does not have access rights they should be refused the connection request, not 'well that didn't work, but have this one instead!'

In this case using domain credentials is completely appropriate since its nothing to do with the web, its local domain users connecting with an internal accounting application within one building. The domain users should be able to access the application, but since the admin login for one specific machine (not the domain administrator) is never used normally, it is invalid that the admin should have rights - and they don't on the SQL Server user list - so thats a bit screwed up also.

Richard

richard,

i don't know why you would want to use NT authentication and due to all these issues now, I would just deselect that in the ODBC settings. then use SQL's user name/password. That'll fix all of the issues. Security and control. If anyone can access anything the only way they can do it is through SQL's user list.