Last post Dec 04, 2009 05:37 PM by FileFoundException
Dec 03, 2009 03:58 PM|ahillyer|LINK
Since I've been put on a project that connects to our bugtracking / work order system (TrackIt) and I've been told that there is no way to get at that database except via an ODBC connection, I've had to create that connection, create a linked table in Access,
and then create an AccessDataSource to get at it. I have to tell you...I'm in unfamiliar territory with this stuff so any and all help will be appreciated. First off, is there a way that I can just connect directly to that ODBC connection through .Net 3.5
and a SqlDataSource? I've tried but had no luck there.
In any case, I was able to create an ODBC connection both on my client machine and the server and they have tested successful in connecting to the server. I have created a linked table in Access with that ODBC connection and an AccessDataSource. When I
try to run it from my local machine (running it via F5 from VS), I can connect no problem. However, when I upload it to my server (Server 2003 / IIS 7 / .Net 3.5), I'm getting the error "OleDbException (0x80004005): ODBC--connection to 'Trackit' failed."
Any ideas on that? If this is a permissions error, please be specific in what I need to add permission-wise because I'm not much of an admin and will have to pass it along (i.e. which account would this be running under to give permissions and what types
of permissions need to be added and where, etc.)
Preferably a more direct way where I can eliminate the Access database would be ideal.
I don't really see how it would make a difference but here is my AccessDataSource:
Here are the settings from the DSN (it is a System DSN on both client and server - should it be User? Is that perhaps the problem?):
How should SQL Server verify the authenticity of the login ID?
- With Windows NT authentication using the network login ID.
-Server name and alias are the same and are correct
-Dynamically determine port
Connect to SQL Server to obtain default settings for the additional configuration options.
-This is checked
"Change the default database to" and "Attach database filename" are not checked
"Use ANSI quoted identifiers" and "Use ANSI nulls, paddings and warnings" are not checked
The rest are not checked except "Perform translation for character data"
Dec 03, 2009 10:15 PM|FileFoundException|LINK
You should be able to use the SqlDataSource control see the remarks section here
you just need to get the right connection string to your database find your db here http://www.connectionstrings.com/ and use the correct syntax.
At any case, what happens sometimes is that the name of the DSN in the source code does not match the actual DSN name on the server. Also the user under which your asp.net app is running needs read+write access to the mdb file.
Dec 04, 2009 09:17 AM|ahillyer|LINK
Thanks but I've already looked at many of those types of resources (including connectionstrings.com)...I just couldn't seem to get it to work which is why I switched to the Sql Server (Source) --> DSN --> Access --> AccessDataSource model. Any suggestions
on which specific string to use (I swear I must have tried them all twice)? Or, perhaps, what this error means and how to remedy it?
Dec 04, 2009 11:34 AM|FileFoundException|LINK
To verify, I did a test using SqlDataSource connecting to a DB through ODBC and it works.
<asp:SqlDataSource ID="sqlds" runat="server"
ConnectionString="DSN=MyDSN" ProviderName="System.Data.Odbc" SelectCommand="SELECT TOP 10 * FROM schema.MyTable"
Another approach is to use OleDb instead by providing the appropriate connection string instead of the DSN.
Dec 04, 2009 11:48 AM|ahillyer|LINK
Right...like I said, it works on my local machine (I thought I mentioned that...perhaps I missed it) but not when I upload it to the server.
Can you please provide the appropriate connection string? I haven't had any need to make that type of connection in the past so I'm not sure what it would be.
Dec 04, 2009 11:49 AM|ahillyer|LINK
Ignore the first part of my last post...I didn't realize you provided a SqlDataSource (for some reason, I thought it was an AccessDataSource). Thanks, I will try that but I think I'll run into the same issues that I did with the other for the DSN. I'll
try it nevertheless.
Dec 04, 2009 12:02 PM|FileFoundException|LINK
In case the example does not work, what database engine are you trying to connect to?
Dec 04, 2009 12:35 PM|ahillyer|LINK
Sql Server. All I know is what I mentioned in my first post. I can't get any more info out my admins so that's what I have to work with (unfortunately).
Dec 04, 2009 04:12 PM|FileFoundException|LINK
It seems this is an authentication issue. If it's MS Sql Server, why not use ADO.NET. Just out of curiosity what happens (what exeption message is thrown) if you execute the code below after putting the correct server database names in connectionString
public static void TestConnection()
string connectionString = "Data Source=Server;Initial Catalog=TrackIt;User Id=User;Password=PASSWORD";
SqlConnection cn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("SELECT TOP 1 * From sys.tables", cn);
SqlDataReader reader = cmd.ExecuteReader();
string col = reader.ToString();
Dec 04, 2009 04:55 PM|ahillyer|LINK
I absolutely WISH I could...that's how I've always connected to other databases. But, there are several issues: I can't even see the server except via the DSN that is set up. Secondly, there is no user name and password that I can use. My only option
is DSN. That's what they've given me. Stupid I know. That's why I'm out there in left field with this one. Trust me, I absolutely wish I could...I do those all day and they are sooooo straightforward!
Thanks for trying though!
Dec 04, 2009 05:37 PM|FileFoundException|LINK
They're very straight forward. I was just curios. I think your admins want to use Windows Integrated Mode to authenticate to the server and it works with Access because the process is running under a domain creditionals that have access right to the server;
your web app however runs under a different user that does not have access. So you may have to turn on impersonation from web.config + use Windows Integrated Mode for IIS authentication for that particular web app and disable anonymous access. This will work
if both the SQL Server and the Web Server are on the same machine otherwise you'll run into double-hop issue.