How to add IIS_IUSRS to SQL Server

[David Clarke]

Do any of you guys know how to add IIS_USRS to SQL Server?

For the past 25 years I have used a user name and password combo to have my PowerBasic CC CGI applications access SQL Server.

So today I figured I would try to use "Integrated Security" as Microsoft says it is a best practice for web servers accessing SQL Server. Not so easy! Especially when you don't know how to do it.

I tried the obvious stuff like trying to make a Login for WEB-SERVER-1\IIS_IUSRS and BUILTIN\IIS_IUSRS and IIS APPPOOL\cerrutihome.com - that is the name of the application pool.

You would think Google might have a pointer or two.... Perhaps I am not asking the question correctly.

[Stuart McLachlan]

Are you running Active Directory on the server?
Do you have IIS Server rurnning on your web server?
Are your CGI applications running under IIS ?

Is the SQL Server running on the same web server machine

(And what's this got to do with PowerBASIC programming? )

[David Clarke]

Stuart McLachlan No AD, WebServer and DB Server are on same machine for the moment.

[Stuart McLachlan]

Stuart McLachlan No AD, WebServer and DB Server are on same machine for the moment.

Are your CGIs running under IIS ?

[David Clarke]

Stuart McLachlan Yes.

[Stuart McLachlan]

This one is for asp.net, but it may point you in the right direction:
https://learn.microsoft.com/en-us/pr...5788z(v=vs.100)

I generally work with Apache servers, mySQL and PHP rather than Windows servers and CGI so can't add much more here.

[Stuart McLachlan]

Thinking about it, if you only allow connection to the SQL server from its own IP address, there is little or no benefit in using integrated security.

[David Clarke]

Stuart McLachlan What if I am using named pipes or memory.... TCP inside the same box... What is the deal with that? I need to play with the transport settings....

[Bud Durland]

I always install SQL in mixed authentication mode, and create a user name and password for use by my programs, Then use ODBC to connect. I use SQL_Tools, so putting it in the connection string in the source is pretty easy.

[David Clarke]

Bud Durland - That is what I have been doing my self for years. One of my clients runs one of my applications using windows security and I was trying to simulate it at my office.

[Mikael T]

I don't think that this will help you so much, but here it is anyhow

When accessing my SQL DB without DB users I use the following ODBC string.
DRIVER=ODBC Driver 11 FOR SQL SERVER; Trusted_Connection=Yes; SERVER=MyServerName\MyDB

and with DB users
DRIVER=ODBC Driver 11 FOR SQL SERVER; Uid=[User]; Pwd=[Passw]; SERVER=MyServerName\MyDB

[David Clarke]

Thanks guys - The issue is (I think) is that the web server is a different user.

[Bud Durland]

Thanks guys - The issue is (I think) is that the web server is a different user.

Most likely. Good luck sorting it out
(this is why I continue to use Apache for hosting my CGI's)

[Carlo Pagani]

David you just need to add the user defined for the app pool to the trusted login on SQL. Not the best of secure ways but that's the ID your CGI app inherits.If your pool is called XXX then the user IIS AppPool\XXX

It is better that you configure a serice account for your CGI APP and have a Hash for logins. Better still, use FASTCGI as the performance difference is staqggering.