how to access sql server from asp page

Question

We have a legacy, homegrown timesheet system (ASP, microsoft sql server 2005) that I need to clone to another computer for backup purposes. (I know very little about this stuff, so pleas be gentle)

I've got most of the pieces in place (IIS, Sql Server, table import / creation). But, the ASP page to access the timesheet pages is choking on access to the sql server.

here is the line it's crashing on: conn.open Session("sConnStr")

This is the connection string;

sConnStr = "Server=MYSERVER-D01;DATABASE=MYDATABASE;UID=MyDatabaseUser;PWD=MyDatabaseUser;QuotedID=No;DRIVER={SQL Server};Provider=MSDASQL"

This is the error:

Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified /mydir/mypage.asp, line 17 <== shown above

Note that am able to access the db on SQL Server with Windows specified as the authentication using Server Management Studio. However, when i try using SQL Authentication, I get the message "The user is not associated with a trusted SQL connection".

Questions:

• How do you set up the user / password on SQL Server 2005?
• What is the default driver, and do i need to get it/set it up?
• When it talks about the data source name ( see "MYDATABASE" in the above mentioned connection string), is it talking about one of the entries you see under "Databases" on the management studio object explorer?

Thanks for you responses! So far, no luck. I've managed to access the database via management studio object explorer, by doing this;

1. Enable SQL Authentication:

   ....Solution To resolve this issue, follow the instructions to set User Authentication. SQL Server 2000:

   Go to Start > Programs > Microsoft SQL Server > Enterprise Manager Right-click the Server name, select Properties > Security Under Authentication, select SQL Server and Windows The server must be stopped and re-started before this will take effect

   SQL Server 2005:

   Go to Start > Programs > Microsoft SQL Server 2005 > SQL Server Management Studio Right-click the Server name, select Properties > Security Under Server Authentication, select SQL Server and Windows Authentication Mode The server must be stopped and re-started before this will take effect..."

2. And this;

   Change the owner to the one being used to access the db

   Microsoft SQL Server Management Studio

   Right click the DB, change the owner

But I'm still getting exactly the same error message!

Answer 1

To create a new user and assign it to a database you'll need to do the following,

1. In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.
2. Right-click the Security folder, point to New, and then click Login.
3. On the General page, enter a name for the new login in the Login name box.
4. Select SQL Server Authentication. Windows Authentication is the more secure option.
5. Enter a password for the login.
6. Select the password policy options that should be applied to the new login. In general, enforcing password policy is the more secure option.
7. Click OK.

You will then want to assign that login to a database by creating a new database user,

1. In SQL Server Management Studio, open Object Explorer and expand the Databases folder.
2. Expand the database in which to create the new database user.
3. Right-click the Security folder, point to New, and then click User.
4. On the General page, enter a name for the new user in the User name box.
5. In the Login name box, enter the name of a SQL Server login to map to the database user.
6. Click OK.

You'll want to make that user the db_owner. Run the following against your database.

sp_addrolemember @rolename = 'db_owner', @membername = 'usernamehere'

Then use the following connection string format to connect to the database.

Data Source=ServerAddress;Initial Catalog=DatabaseName;User Id=UserName;Password=UserPassword;

Answer 2

If you have a trusted connection from the login that IIS is using the the machine that has SQL Server running on it I would avoid using Username / Password and declare that the connection is trusted in your connection string:

sConnStr = "Server=MYSERVER-D01;DATABASE=MYDATABASE;UID=MyDatabaseUser;PWD=MyDatabaseUser;QuotedID=No;DRIVER={SQL Server};Provider=MSDASQL;Integrated Security=SSPI"

This is to illustrate the change, but in practice you may need to vary the connections string a bit more than that, have a look at http://www.connectionstrings.com/sql-server-2005 for examples.

When it talks about the data source name ( see "MYDATABASE" in the above mentioned connection string), is it talking about one of the entries you see under "Databases" on the management studio object explorer

Yes, your entry for "MYDATABASE" should be the exact name of the database that you see under "Databases". Make sure that you have the "Server" correct too.

Answer 3

Microsoft OLE DB Provider for ODBC Drivers error '80004005' 

[Microsoft][ODBC Driver Manager]Data source name not found and no default driver specified.

This usually happens in one of the following scenarios:

* you referenced your connection incorrectly (e.g. spelled the DSN name, or one of the DSN-less string components wrong);
* you referenced a DSN that doesn't exist;
* the user connecting to the DSN or DSN-less connection doesn't have access to the information stored in the registry (see KB #306345);
* you used an English or localized driver detail for your connection string when your system is not set up in that language (see KB #174655); or,
* you are missing the connection string entirely (this can happen if you maintain your connection string in a session variable, and your sessions aren't working; see Article #2157).

Here is the link to the above article (note it is extremely detailed). link

To answer the last question, MYDATABASE is calling a database by name. If you use 'MYDATABASE' in your string, you will need a database named 'MYDATABASE' in SQL Server.

Answer 4

This connection string should work fine with ASP if this is a SQL server. Replace your values before using obviously.

sConnStr = "provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog=YourDBName;UID=YourUserName;PWD=YourUserPWD;"

Answer 5

The easiest way I have found to deal with these issue is to create a udl file. On your desktop create a new text file and rename it filename.udl. Double click the udl file. Click the Provider Tab > select Microsoft OLE DB Provider for SQL Server > Next. Using the connection tab you should be able to connect to your database. Once test connection succeeds click ok. You can now open the file in a text editor and copy and paste the line that start Provider... to your asp file. You should end up with sConnStr = "Provider..textfromUDLfile"

MSDN - Creating and Configuring Universal Data Link (.udl) Files

Answer 6

I suggest that you create a DAL (Data Access Layer) that can do all the connection stuff for you. Just passit your command an dit can open and close your conenctions and such. In any app you wan tto abstract these different layers as much as posible and that means that your aspx page should call to an object when has the methods that hten get handled by the dal and make the database calls.

Here is the format for connection to the DB. You can put the connecitn string in the web.config file or even do it in code using hte connectionstringbuilder.

you also need to make sure that your project includes the system.data.sqlclient library otherwise this won't work.

The entry in the web config file looks something like this.

<add name="ConString" connectionString="Data Source=localhost;Integrated Security=True;Initial Catalog=&quot;DBtouse&quot;;Persist Security Info=True;" providerName="System.Data.SqlClient"/>

or

<add key="ConString" value="Server=localhost;user=username;password=password;Initial Catalog=MyDBtouse;pooling=false"/>

the code behind loks like this:

Dim MyConnection As Data.SqlClient.SqlConnection
Dim Constring As New SqlClient.SqlConnectionStringBuilder
Constring.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("ConString")
Constring.ConnectTimeout = 30
MyConnection.ConnectionString = Constring.ConnectionString
MyConnection.Open()

'Execute code here

MyConnection.Close()
MyConnection = Nothing