Windows, IIS and ASP gurus, please help!!!

I am at my wit's end here!!!

I have developed this wonderful application that is supposed to get the user's windows NT log in, compare that to the database and then go straight into the application.

It works well on the local PC, Windows 2000, SQL Server 2000, IIS 5.0. However, on a client-server environment, the application does not recognise the windows log in. Please do not tell me to do it in .NET, it HAS to be done in ASP.

Can anyone please come up with a solution that will work? Entering domain\username in the database is useless because the application will be used on three different domains!!!!

Here is the code that is supposed to do the magic, please pay particular attention the the lines that differentiate the servers:-

If Request.Servervariables("REMOTE_USER") = "" Then
   Response.Status = "401 Unauthorized"
  'response.redirect("nologon.asp")      
elseif request("REMOTE_USER") <> "" then
    username = Request.Servervariables("REMOTE_USER")

'this part differentiates the  three different servers
'IF request("SERVER_NAME") = "localhost" then
servername = left(username, 13) '-- removes domain OR netbios name alter to suite length of domain name.
'END
'if request("SERVER_NAME") = "MUNIN" then
'servername = left(username, 6)
'end
'if request("SERVER_NAME") = "CABWEB" then
'servername = left(username, 6)
'end if
      
userid = lcase(replace(username, servername, ""))
      
strSQL="SELECT * FROM Users where username = '" & userid & "'"
set rsUsers=Server.CreateObject("ADODB.recordset")
rsUSers.open strSQL,objConn,3,3
      
If rsUsers.recordcount = 0 then
'response.write "There are no users matching the user name " & userid
response.redirect("nologon.asp?mid=1")      
Else
while not rsUsers.EOF
session("userid")=rsUSers("user_id")
session("user")= rsUSers("first_name") & " " & rsUSers("last_name")
session("team_id")=rsUsers("team_id")
session("loggedin")= 1
session("style")="pms_medium.css"

'enter path for uploaded files
if request("SERVER_NAME") = "localhost"
session("upload_directory")="uploads"
'ELSEIF request("SERVER_NAME") = "MUNIN" then
'session("upload_directory")=""
'ELSEIF request("SERVER_NAME") = "CABWEB" then
'session("upload_directory")=""
'END IF

rsUsers. MoveNext
wend
response.redirect "projects/projects_home.asp"
end if

end if
      

Thanks.

Nicky
nicky2kWeb DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RanjeetRainCommented:
Where are you stuck? How do you collect the data?
0
nicky2kWeb DeveloperAuthor Commented:
I am stuck when the application is being accessed remotely via a client. Where IIS and SQL Server reside on the same box, the application is okay. Once IIS and SQL Server reside on 2 separate boxes, the application fails.

The data is pre-populated in the database.
0
RanjeetRainCommented:
I do not understand what is failing and why. I will tell you how a 3 tier application is designed.

(1) UI - User interface is loaded from any client from practically any part of the world where your network is reachable. Apparently, this is okay here.

(2) Middle tier - Your web server (IIS). Problem is most liekly to be here. Do you have a DSN on this system that connects you to the SQL server? How do you construct the connection object objConn? Is that with a DSN? If so, you just need to see if you have localhost specified as the DB server name. If that is the case, specify the proper hostname of the SQL server. Depending on your network design you might have to use fully qualified domain name. Check with your Network Admin if yo are unsure about this.

(3) Back end - This should be okay. YOu may carry out a small test to verify this. YOu may try connecting from the client directly. If you are able to connect directly your SQL server is accessible from outside.

Let us know where do you now feel problem is.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

abdullah716Commented:
First, a small note: For code claity, you may use the split function to get the domain and the userid out of the "domain\username" string.

domain = Split("domain\username", "\")(0)
userid = Split("domain\username", "\")(1)

Now, when SQL and IIS reside on 2 separate boxes, you may have an authentication problem. In your connection string, you can either:

- Include an SQL Server username and password, or,
- Make sure IIS anonymous access is disabled, change the connection string to allow to use Trusted Connection, and finally give the users access to the SQL database (e.g. From Enterprise Manager)

0
nicky2kWeb DeveloperAuthor Commented:
The connection string is something like this:-

set objConn=server.createobject("adodb.connection")

Conn="Drive=r{SQL Server};SERVER={Actual server name};DSN={actual DSN name};DATASOURCE={Actual datasource}"

objConn.open Conn

Am I to understand that you want me to replace the Server name with localhost?
0
RanjeetRainCommented:
Why not specify a UID and PWD in the connection string as well.

Did you perform connectivity test? Basically, the account with which IIS is being run you should be able to connect to SQL server and use the data as you need.
0
nicky2kWeb DeveloperAuthor Commented:
Where the application all sits on one box runs in my office on my local PC and I had no problem with connectivtity.

I now had to ftp all my files to a third party that handles networking for the client I am building the application for.that is where I encountered the problem.

Will try username and password and hope that it works.

0
nicky2kWeb DeveloperAuthor Commented:
RanjeetRain,

The adding of username and password seems to have helped.
That solves the problem, but let me be a bit sneaky while I am here.

I mentioned that SQL Server sits on a separate server to  the application files. I also need to access two different databases. So, being the extremely clever person that I am (as is evident from all my questions, ahem), I wrote the following SQL string:-

SELECT (UnitID), (UnitDesc) FROM [CabWebDirectory]..[UNITS]

hoping that it will be enough. Was it? Nope!!!

I keep getting the error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'CabWebDirectory..UNITS'.

Er...what???!!!
0
nicky2kWeb DeveloperAuthor Commented:
By the way, done the obvious, removed brackets.
0
nicky2kWeb DeveloperAuthor Commented:
Oh, and connection string is:-

Conn="DRIVER={SQL Server};SERVER={Server 1 name};DSN=pms;DATABASE={PMS};uid={username};pwd={pasword};"
Conn2="DRIVER={SQL Server};SERVER={Server 2 name};DSN=dsn2;DATABASE={Database2};uid={username};pwd={password};"

objConn.Open Conn,Conn2
0
RanjeetRainCommented:
YOur connection string seems fine. Retouch the SELECT. See the help to find out how to refer to a table with a full path. It may take a form like: UserName.DatabaseName.TableName

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nicky2kWeb DeveloperAuthor Commented:
Thanks, it worked!!!
0
RanjeetRainCommented:
Glad you solved it!
0
abdullah716Commented:
This is not fair. I should be awarded at least some points. I answered your question before Ranjeet.

[quote]
- Include an SQL Server username and password, or,
[/quote]

and you confirmed that this solved your original problem:

[quote]
The adding of username and password seems to have helped.
That solves the problem, but let me be a bit sneaky while I am here.
[/quote]

0
nicky2kWeb DeveloperAuthor Commented:
Can points be split?
0
abdullah716Commented:
Split? With respect to Ranjeet, but he just copied my answer. The fact that he answered your "Select" problem, which should've been asked in a separate question, doesn't give him the right to recieve points here.

Anyway, I leave it in your hands and to the PageEd.

0
nicky2kWeb DeveloperAuthor Commented:
Can you please go here http://www.experts-exchange.com/Web/Q_21227588.html to receive your points?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.