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
Attached image(s)
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 |
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. |
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. |
QUOTE |
In this case the default database is the clients main live production system, so it appears to be a glaring security risk. |
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.
Attached image(s)
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. |
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. |
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.
Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)