SQL and ODBC operate diffrently with Trusted Connection?

[Joe Cool]

I am working on an app where I work that makes a connection to two
different databases. These databases may or may not reside on the same
server. One database is "our" database in that our suite of software
operates on the data in it. The other database is maintained by a
piece of third party software. My app only needs to pull data from it.

On one form in the app, there are two dropdowns. One to select whch
server the third party database resides on and the second to select
which database on that server is the third party database.

I populate the server dropdown with the SqlDataSourceEnumeration
class.

When the user then selects a server, the databases dropdown is
populated. The SqlDataSourceEnumeration does not require a separate
connection object but I am using the SqlConnection.GetSchema method to
obtain the list of databases, and it obviously needs a SqlConnection.

Since every other connection to the third part database is using ODBC,
I wanted to convert the populating of the databases dropdown to use
ODBC also, especially since the OdbcConnection class also supports a
GetSchema method.

But when I converted to ODBC (and yes, I modified the connection
appropriately), I get a permission error when trying to invoke the
GetSchema method.

Both connection strings specify to use a Trusted Connection.

So why can the Sql connection get the data and the Odbc connection
can't?

 

[Mr. Arnold]

But when I converted to ODBC (and yes, I modified the connection
appropriately), I get a permission error when trying to invoke the
GetSchema method.

Both connection strings specify to use a Trusted Connection.

So why can the Sql connection get the data and the Odbc connection
can't?

A Trusted Connection has nothing to do with permissions. In either of your
usage ODBC or SQL case, the solution would have blown-up on the connection
if the user account used to logon to SQL server was invalid to logon to SQL
server.

You're not indicating that there is no connection problem, and the
connection is good. You are indicating that you have a permissions problem,
which is based on the context of the user account used to access SQL server.
Something is wrong with the user account permissions when using the
functionality you're talking about it seems to me and not a Trusted
Connection issue.

 

[Cor Ligthert[MVP]]

You ask why. Maybe because that SQL client is not created for nothing, it
has more possibilities then the old verhicle ODBC

It is always avised not to use ODBC anymore as you can use OleDB or even
better SqlClient as it is about SQLServer

Cor

 

[Erland Sommarskog]

Since every other connection to the third part database is using ODBC,
I wanted to convert the populating of the databases dropdown to use
ODBC also, especially since the OdbcConnection class also supports a
GetSchema method.

But when I converted to ODBC (and yes, I modified the connection
appropriately), I get a permission error when trying to invoke the
GetSchema method.

What is the exact error message?

You could use Profiler, to see which statements that the providers
sends to SQL Server.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx