Connecting to SQL from Access

I have users who currently query a SQL database using Access and an odbc
connection. The database is being moved to a less secure server, and we are
looking for the best way for users to connect to this database so they can
do their own queries. They like Access because they can select the tables
and don't have to remember t-sql code, and I wouldn't want to give them sql
query anaylizer anyway. What would be the best way to do this? Eventually
we will be creating programs for them to access their data, but that could
be a long time in coming, and there are always queries we cannot anticipate
and therefore cannot provide an application for.
Thanks. Any ideas are appreciated.Access works pretty well. We've used access project files (.adp) to
allow users to query databases. .adp files utilize OLE DB connections.
You can also appropriately permission the login that the Access .adp
uses to access the sql server database. Let me know if you need more
specific guidance, I'd be happy to help out.|||Access works pretty well. We've used access project files (.adp) to
allow users to query databases. .adp files utilize OLE DB connections.
You can also appropriately permission the login that the Access .adp
uses to access the sql server database. Let me know if you need more
specific guidance, I'd be happy to help out.

Connecting to SQL Express on a hosted site

Folks.

I just moved my domain to GoDaddy.com. When I run my website locally, everything works fine.

When I ftp it to my server space on GoDaddy.com, I get a "remote connection" error. Does anyone have an example of a connection string I should be using to connect while on the server?

Thanks!

From looking at their website, they do not appear to offer SQL Server2005 or SQL Server 2005 Express. That'd be why you're getting theerror you are. I'm getting the same dang thing on my hostingservice - 1PlanHost.com. They, for some reason, will not installSQL Express... even though ALL of these starter kits, etc. are using itby default. It's making things quite bothersome for us developers!

If anyone figures out a way to force SQL Express to run within a shared hosting enviroment, let me know!|||

Thanks for your response. GoDaddy does offer SQL Server. You can configure it once you have purchased a hosting package. What they don't offer is any incling of support for it. If I find anything out, I'll let you know.

sqlsql

Connecting to SQL dbfs with existing code

We recently moved our Access databases to SQL and are
trying to get our pages to open the connections to the
server. I've set the system DSNs on the server as needed.
However, no connections are made. After reviewing several
books, microsoft.com, etc...I'm severely frustrated. The
only thing I can think that would possibly be an issue is
that our webserver (Win2003) is not setup as a Primary
Domain Server and we have to connect via http.
Any suggestions are appreciated. THANKS!Does the System DSN connect if you Test it from the IIS server?
If not what is the OS error returned?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Connecting from iis5 ASP.net2.0 to sql2005

I'm trying to move an application I wrote from sql2000 to sql2005. The application is in ASP.NET and works fine using SQL2000 but when I moved the database and change the connect string the page display the message:

Cannot open database "EventTracker" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

If I add the identify tag to the web.config file

<identity impersonate="true" username="myname" password="mypasword"/>

then page comes up and everything works. The problem is I need to have the user's credentials from internet explorer to be used against the SQL database to control access.

I added some lines of code to see if the web page was acting with the web users credientials and it is for things other than the SQL2005 connections.

The following error message shows up in the web server's application log:

Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 7/6/2007 6:50:10 PM
Event time (UTC): 7/7/2007 12:50:10 AM
Event ID: c3d90a99419f49ad8d307930acb5bb71
Event sequence: 13
Event occurrence: 2
Event detail code: 0
Application information:
Application domain: /LM/W3SVC/1/Root/ComputerServices/TruckerViewer-9-128282421386484375
Trust level: Full
Application Virtual Path: /ComputerServices/TruckerViewer
Application Path: c:\inetpub\wwwroot\ComputerServices\TruckerViewer\
Machine name: webserver
Process information:
Process ID: 960
Process name: aspnet_wp.exe
Account name: webserver\ASPNET
Exception information:
Exception type: SqlException
Exception message: Cannot open database "EventTracker" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Request information:
Request URL: http://serverws/ComputerServices/TruckerViewer/Counters.aspx
Request path: /ComputerServices/TruckerViewer/Counters.aspx
User host address: 10.10.17.187
User: $PCA\User4
Is authenticated: True
Authentication Type: NTLM
Thread account name: webserver\ASPNET
Thread information:
Thread ID: 1
Thread account name: webserver\ASPNET
Is impersonating: False
Stack trace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at TruckerViewer.Counter1.Page_PreRender(Object sender, EventArgs e) in \\webserver\c$\Inetpub\wwwroot\ComputerServices\TruckerViewer\Counters.aspx.vb:line 61
at System.Web.UI.Control.OnPreRender(EventArgs e)
at System.Web.UI.Control.PreRenderRecursiveInternal()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

The SQlserver's application log records two messages;

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 10.10.94.136]

and

Login succeeded for user 'NT AUTHORITY\ANONYMOUS LOGON'. Connection: trusted. [CLIENT: 10.10.94.136]

This exact same code works with SQL2000 but not with SQL2005. What am I missing? Any help would be appreciated.

If you want to use the credentials supplied by the user (through internet explorer), make sure the application is setup to use Windows Authentication.

Authentication Settings:

http://samples.gotdotnet.com/quickstart/aspplus/doc/authandauth.aspx

Also make sure you are setup for Impersonation:

http://samples.gotdotnet.com/quickstart/aspplus/doc/impersonation.aspx

The fact that the login failed for the Anonymous Login account tells me your configuration isn't quite correct (for what you are trying to do).

Evan

|||As you already turned on impersonation, you will have to configure your website NOT to use anonymous connection as this is always prefered before using authenticated connections.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--|||

That's not working... The idea is that the web "internet explorer" user's domain\account are to be used by the web server to gain access to the SQL database on another server. This isn't happeining. If I add code on the web server to reference a file on the web server that on the "internet explorer" client's account has access to, it works. If I deny access to that file for that web user they don't get access. So I think "impersonation" is working. However, when that file is stored on another computer (via a network share) the other computer complains that the "anonymous user" doesn't have access. If I grant the anonymous account access it works but I don't want anonymous users to have access, only those users who have access to the file (or in reality the SQL database). Is there something I need to do to enable the web user's account to pass through the web server to the SQL server? Can this be done at all?

|||

As Jens mentioned, you will want to disable anonymous connection for the web site/page in IIS that's hosting your ASP.Net code. This is on top of configuring your ASP.NET application to support Integrated authentication and impersonation. You can change the web site configuration using the IIS manager tool.

HTH,

Jimmy

connect to SQL server 2005 databse using perl

we moved recently to SQL server 2005, i have perl scripts that i use to connect to the database, retrieve information and updata datas. since we moved the perl scripts are unable to connect to the database. I created the system DSN in the odbc manager that is used to connect to that database. this is the error message we are getting when we try to cnnect to the databse :
DBI connect('billing3','dbo',...) failed: [Microsoft][ODBC Driver Manager] Data
source name not found and no default driver specified (SQL-IM002)(DBD: db_login/
SQLConnect err=-1) at night_process.pl line 30

the perl script that tries to connect is :
#!/usr/bin/perl -w
use File::Copy;
use Date::Calc qw(Today Add_Delta_Days);
use Cwd;
use Net::FTP;
use DBI; # database connection ;

$DSN="billing3";

$user="dbo";
$pass="toto";

#connect to the database

$dbh = DBI->connect("DBI:ODBC:$DSN", $user, $pass);This is an SSIS forum. Your post has nothing in regards to SSIS so I would advise posting to another forum or several others as it is unclear which forum would be best suited to answer this question.

Thanks,
Matt