Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

is it possible to connect ASP website which is on web server to connect to SQL server 2008 database which is on another database machine? In my case the database machine has windows XP SP1 installed on it, so it is just like desktop machine on the network. Both machines are on the same domain. If possible how can we setup the connection? What security settings are required on database side? what network settings are required? What code can we use on ASP file? Is there any test that we can perform with the simple connection string code?

I had used this code

Provider=SQLOLEDB.1;Data Source=myDbServer;Initial Catalog=myDatabase;Uid=myUsername; Pwd=myPassword; I got this code from the .udl file where test connection works but it does not work when I put it on dataconn.inc file. Why? I have tried various connection strings but it does not work. I am confuse.

How can I debug the datconn.inc file? it is just the text file. Can I use any other asp file rather than using dataconn.inc? I am conecting to database to check my login credentials and then log into this website and I am getting http 500 error.

share|improve this question

migrated from stackoverflow.com Oct 15 '11 at 6:33

This question came from our site for professional and enthusiast programmers.

    
Yes. It is all a question of permissions. There are many internet articles dealing with this topic. –  Mitch Wheat Oct 11 '11 at 0:33
    
what permission should I enable? In asp.net if you just go to project properties and check settings and in settings you can open the dialogue box which you can use to manually and visually add values in it like server name, database name, network library, database password and username and make the connection and it will work. But in ASP can we use any thing like that? I have include file which is just a text file and have asp code on it. I do not know how to change it to make it work. Any test we can perform? –  user680865 Oct 11 '11 at 0:37
add comment

2 Answers 2

Yes of course you just need so specify the connection string of the sql server that you need to connect on web.config of your web application.

share|improve this answer
    
I am using dataconn.inc file in classic asp. There is no web.config file on anything like that on this website –  user680865 Oct 11 '11 at 0:43
    
social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/… Check this. –  Crimsonland Oct 11 '11 at 0:49
    
I have used .udl file to create connection and connection test was successful. .udl file also generates the code. I used that code in my asp file and it does not work. Is there any other test i can do? –  user680865 Oct 11 '11 at 12:20
add comment

The simplest way to do this is give IUSR_[computer name] (where that is your web server machine) read/write access to the relevant database (use the datereader and datawriter builtin roles, DON'T use dbo).

If you are using stored procedures, you will need to grant execute permissions to IUSR_[computer name] as well.

In addition, eet you connection string to use a Trusted connection:

"Data Source=myServer;Initial Catalog=myDataBase;Integrated Security=SSPI;"
share|improve this answer
    
IUSR [computer name] where you want me to write this? on new query IDE of SQL server management studio? –  user680865 Oct 11 '11 at 0:52
    
I think you need to perform an internet search... –  Mitch Wheat Oct 11 '11 at 0:53
    
so you are talking about, Go to Control Panel > Administrative Tools > Internet Information Services > local computer > Web Sites > Default Web Site > cgi-bin. Right mouse click to select "Properties". On the "Directory" tab, you will see "read" and "write" checked or not. –  user680865 Oct 11 '11 at 1:56
    
No: I am talking about giving database access permissions to the account that IIS runs under. –  Mitch Wheat Oct 11 '11 at 2:22
    
I do not understand it. Can you give better explanation or example? –  user680865 Oct 11 '11 at 3:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.