question about connecting odbc to sql through php script

... but its giving him errors accessing this information...

What kind of errors? And any more info on how hes trying to do it (maybe some tid bits of the php source) would be nice

cheers

Warning: odbc_connect() [ function.odbc-connect ]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'., SQL state 28000 in SQLConnect in C:\(path to the php file) on line 12
Connection Failed:

now we don't need to have the odbc driver setup correctly on the local machine, do we? because his thinking is clients can have access outside the office to open this web page, be able to type in a case # and pull up certain information about that case through read only. hence the reasoning for adding a user and password in the script, so when someone tries to pull information, it will use the credentials of the user in the script and not the user logged on.

i dont know if this can be done or not, but for ex: joe smith is on his machine, he logs on to windows with either his name and password (for domain purposes) or just default administrator credentials (if hes home). regardless how he logs onto windows, once he opens a browser and browses to that web page, he can log onto the web page with his credentials (just so hes authenticated) then when pulling case information, it uses the credentials in the script so he has read only access.

so we need to connect php to the sql DB using odbc, is that possible and any more info on how that can be setup? thanks for the help! if you need more info from me, please let me know.

Ok, so let me get this straight since I dont get what you mean, sorry

You (or your boss, whatever) wanna be able to create a user account, so that this new user can log on into the database and get some information out of it, right? If so, Im sorry If Im gonna treat you like the average joe, but I dont know how much you know

First, I have to point I have never worked with IIS server before, so I dont know much. Most of my job is done through apache,php and mysql/postgresql.
There are basically two ways to give some user access to a database

1. Creating the user IN the database itself, this can only be accomplished by the DB server root (probably your boss). Now, once the user has access to the DB, he/she can pull off information out of it. This can be achieved through a database frontend made in some language (c/c++,C#,Java,perl, whatever).
This is usually a bad approach... Is this the one your boss is trying to make?

2. Creating a SINGLE,UNIQUE user in the database, with read-only access to the required DB. Then, you create "accounts" from php code, just like you registered to daniweb forums with the username jime0726, and I, with darklordsatan. Those ARE NOT database accounts, they are simply some tuples defined in a given table, like this

User ID/login/password/...
8899/jime0726/yourpassword/...
4456/darklordsatan/mypassword/...

And so on...This is the most typical approach, that way, you create a Web interface in php for instance, then log in as the SINGLE,UNIQUE user in the DB (this is done in the php script, none of this code is going to be readable by the client - aka the user thats using the browser), and once you're in, authenticate the user through an already created tuple in the table (jime0726, darklordsatan, etc...). Once the user authentication was successful, then he/she can access the information in ways provided by the Web interface.

If I lost you, here it goes again
a) A user is created in the database, with limited permissions for security sake (you could just use the root user as well, but if someone breaks into the server and somehow reads the php script, then he could see the password for the root, and hell begins. Also, there are some issues with sql injection for example, when, a script uses the root account through a php script, then the hacker can issue delete/drop commands, and hell arises again).
Notice this can only be done if you have total access to the DB server (aka, you are the server administrator), which I think is the case here.
In some other instances, like when you get some cheap webhosting and a DB is already created for you, there are no privileges to create new users, so you have to user the provided account...
b) A table in the database is created, it can be called users or whatever, and basically will contain information of the account, like an Id, a login, the full name, password, location, sex, bla bla
c) A web interface for registering new users is created (this can also be done manually though), and basically your boss creates accounts at will, that will be simply tuples in the table.
d) A web interface is created to allow user authentication (based on the info in the table users of whatever you called it), and then, if authentication is correct, the user is given permission to access desired information. This can be done by anyone with correct login/pass anywhere in the whole world, just like you can log on to daniweb forums from a dark cave in africa, provided you have a PC and internet connection, hehe

Thats it, so, whats the approach your boss is trying to create?
Also, notice, the ODBC driver must be correctly installed in the server you're running the script on, again, I said I have no experience with IIS, but from my experience with mysql and postgresql, the driver is basically a *.dll you have to "activate" in the php.ini file, and thats it.

One last thing, the error you pointed there, maybe it has something to do with the fact you cannot currently log on as an anonymous user to the DB server, you have to specify an user account (root, or another one, I hope you read 1 and 2 for a clear explanation) in the php script.

Im sorry if this is just a lot of BS, but this is as far as I can get. Please give me your thoughts or relevant information I might be missing here.
Also whats the database server youre using? IIRC, IIS is just a web server right? And I didnt know it supported php, just ASP

Cheers

thanks so much for the info! and i understand about the average joe, thats totally cool :) i'm actually server administrator but dont do ANY sort of programming, know a little about sql administration, and still learning. so this information was definitely helpfull.

i actually had done just about everything you mentioned above EXCEPT the odbc connector on the server running the php script. i created the sql user with dataread access and used sql authentication on odbc and that seemed to push through! is there any other read or view only access i can use for this new user?

everything seems to be working so far, my manager is just touching up the script now to pull certain data from sql. we took the route of your step # 2 above and that seemed to be good. now one more question though, how can we authenticate the user whos accessing the web page? we dont want to use windows authentication because theyd have to be a member of our domain and if we want clients accessing outside, that wont work. if you have any info on this, that would be appreciated.....

thanks very much for your help though!!

Well, user authentication through windows is no a good idea. I dont know if through IIS server you can get account information from a windows domain, but, my guess is that like with anything else, you cant access user password information, for instance.
The step 2 I talked about is made through a Web based interface, so It can be accesable ANYWHERE in the whole world, provided your web server has a DNS or at least an IP accesible from Internet, aka, its not only locally.

The authentication method I explained in step 2 is very very simple, and is commonly used in most contenet managemente systems, just like daniweb.
You can google for "php user authentication mysql" and get losts of tutorials on how to do it, but since Im in a good mood, Ill tell you how I do it, so, heres my quick tutorial.

1. Create a table in your database, It can be as complex as you want. The example is very simple however.

CREATE TABLE user(
	id INTEGER NOT NULL auto_increment,
	type INTEGER NOT NULL DEFAULT 2,
	login CHAR(12) NOT NULL,
	password CHAR(32) NOT NULL,
	PRIMARY KEY(id)
);

You could add some more fields, such as name, address, location, sex, age, and so on...

One thing here about the passwords, those -for security sake- can be stored encrypted as a 32 character string. This is easily accomplished in mysql, check it out

//So, we receive the login and password sent via POST by the HTML form...
$login=$_POST['login'];
$password=$_POST['password'];

//Here is your SQL connection code

//The query would look something like this
phpquerymethod-whateveritis(...,"INSERT INTO user(login,password) VALUES('$login',md5('$password'))");

Thats it, then the password is stored as md5. Well look at the password comparison for the authentication later.

Now, that done, you have to make a choice here
a) Will you let your users register themselves, or
b) The admin (your boss) will be the only one allowed to do so?

In both case, we need to create now a web interface (just a simple HTML form) to create new users in the table user

2. Lets start making the authentication system.
This can be achieved in two ways, either by the $_SESSION global variable or $_COKIE global.
With the first one, you dont have to care about creating the cokie itself, its done automatically, with the second one, you can do it the simple way (just as $_SESSION) or you can also put some parameters in the cokie, like an expiration time (like in webmail servers where, after some minutes of inactivity, you are logged out) and much more.
I have never worked with cokies before, so Ill tell you how to use $_SESSION (and It will never expire by default)

Lets suposse we are gonna have 2 global session variables in all the scripts, the login for the user and the type.
Lets now pretend the type can be
1 = root (your boss perhaps?)
2 = user (authenticated user)

This is what the field "type" was put in the SQL table in point 1...

Notice, for every script youre gonna make, include the following code at the top

//Lets load the session variables
session_start();
$user_type=$_SESSION['user_type'];
$user_login=$_SESSION['user_login'];

Basically it loads a session (could be the guest session), now, depending on what you want to do with the user type and login in every script, you need to do something with those variables, so lets see the example in the next section.

3. Its time now to create the HTML form to create new users in the database. Now, lets pretend ONLY THE ROOT can enter the HTML form and submit it. If any user could do it, just take away the following code...

//File : registration.php

//Session variables code explained in last section goes here

if(isset($user_type)){ //User IS authenticated
	if($user_type == 1){ //Yeah! user is the root
		/*Here comes the HTML form, lets suppose you put in the action field of the form this:
  		  registration2.php*/
	}
	else{
		echo "You have no permission to access this page";
	}
}
else //Ha! user is not even authenticated (the loaded session variables are "empty")
	echo "You are not authenticated, please do so now";

As you can see, the session variables load code is not harmful in the sense that, if the user is autheticated the variables $user_type and $user_login will be set to some value, otherwise they wont be set ("guest" user)

If your boss wants to give everyone the chance to register an account, not only the admin, then the preceding code would be something like this

//File : registration.php

//Session variables code explained in last section goes here

if(isset($user_type)){ //User IS authenticated
	echo "You are currently logged in as $user_login, so why are you trying to create an account?!";
}
else //Ha! user is not even authenticated (the loaded session variables are "empty")
	/*Here comes the HTML form, lets suppose you put in the action field of the form this:
  		  registration2.php*/

4. Create the user in the database. As the prior example, we said "registration2.php" would be the script receiving the variables from the HTML form, this is simple php variable pass, so I wont explain anything, other than, once you have the variables, you enter the data into the DB table user, like I explained in point 1, in the md5 part. Since the type variable is set to "2" by default, then It means all users from now on wont be given root
priviledged, and thats it.

5. Now, time to create the login script...

//File : login.php

//Session variables code explained in last section goes here

if(isset($user_type))
	echo "You are already logged in as $user_login!";
else //User is a guest...
	//Show some HTML login form, and put in the action field "login2.php

Thats it, now, the script login2.php would receive the variables, then check in the database if the given user exists, otherwise echoes an error (wrong password/login bla bla)

Now, lets suposse there IS a user in the table with the given login/password, what to do now to intialize that user's session?

//"Delete" guest user session
session_unset();
$user_type=[some variable retrieved from the database...];
$user_login=[some variable retrieved from the database...];
session_register("user_type","user_login");
header("Location: index.php"); //Lets redirect the user to the index

Now, the session_register code basically SETS the global session variables to some value... now they wont be empty anymore

6. Time for the logout script

//File : logout.php

//Session variables code explained in last section goes here

if(isset($user_type)){
	session_unset();
	session_destroy();
	header("Location: index.php"); //Lets redirect the user to the index
}
else //User is a guest...
	echo " You are a guest, you cant be logged out!?%#~@!";

And thats pretty much it, sorry for the crappy source code, but I think it serves as a nice beginners intro to what session are
Lets resume:

For every php script, load session global variables. If they are set, its because of a prior call to the method session_register (aka, the user logged in), otherwise, the user is a guest.

Cheers :mrgreen:

I have written 2 PHP classes that may be beneficial for your project, jime0726. For each, I have also written an article explaining how to use the code with plenty of examples.

PHP Database Class with Caching
http://www.tgreer.com/class_db_php.html
This class provides a common interface to MySql, Postgres, and MSSql. HOWEVER, I've not tested the MS SQL functionality, but it should be easy to fix any minor issues that could arise. If you are working with MySql or Postgres, it's tested and working in production code.

PHP Session Management and Password Protection
http://www.tgreer.com/class_session_php.html
This code makes it easy to manage user sessions and makes it easy to do login. The downloadable zip comes with a sample login.php page for you to use as a starting point.

Enjoy.

guys, thanks so much for the information! i'll be working on this for the next few weeks and will be refering back to your ideas here in that time frame....if i come across something i dont understand, i'll shoot up another reply with any more questions i have....

thanks again!!