Here is the connection string as in a ASP:
strConnect =3D "Provider=3DSQLOLEDB.1;User = ID=3DIUSER_MachineName;Trusted_Connection=3Dyes;Initial = Catalog=3Dmy_logon;" & _
"Network Library=3Ddbmssocn;Data Source=3DMyServer"
I have IUSR_MachineName as a User in the database and in Security for = Logins for the SQL 2000 SP3 server.
Since I don't know what the password is for the IUSR_MachineName account = I am assuming I could use TrustedConnection=3D"yes"
But this is not working. I keep getting:
Microsoft OLE DB Provider for SQL Server (0x80040E09)
SELECT permission denied on object 'Users', database 'my_logon', owner = 'dbo'.
In fact if I just use:
strConnect =3D "Provider=3DSQLOLEDB.1;Trusted_Connection=3Dyes;Initial = Catalog=3Dmy_logon;" & _
"Network Library=3Ddbmssocn;Data Source=3DMyServer"
I get the same error. The only way I can connect using ASP is using the = sa account or my Administrator account with the password. What am I = doing wrong and how can I get the account to be used to be the = IUSR_MachineName account given that IIS 5 (Windows 2000 Server SP3) = controls the password and I know not what it is? Thanks.
-- George Hester
__________________________________>>
Since I don't know what the password is for the IUSR_MachineName account I
am assuming I could use TrustedConnection="yes"
No, this won't work... you can't have a trusted connection *and* specify the
userid. You will need to do one of the following things:
(a) use a SQL Server user in your connection string.
(b) if you want to use a trusted connection, you will need to set up the
passwords correctly. The IUSR account on your web server is *only* on your
web server. The SQL Server machine has no idea who IUSR_webserver is, so
even if you add that user to SQL Server, the passwords won't synchronize.
In Windows 2000, I believe there was a setting in IIS to disable automatic
password synchronization for the IUSR account, and override the password.
Otherwise, you will have to change the password in password in
Administrative Tools / Computer Management / Local Users and Groups / Users.
Once the web server has a password for IUSR_webserver that you know, you can
do one of two things:
(i) use a connection string with *only* trusted connection=yes (no userid
information - see a sample at http://www.aspfaq.com/2126) and make sure that
windows authentication is disabled
(ii) add the same username/password as a SQL Server user, and include the
user id and password in the connection string (and remove the trusted
connection bit).
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi Aaron. Ok I see it won't work as I was hoping. Too bad I can't just =send the IIS generated password to SQL. That would make life so much =easier.
But one thing I am noticing. If I add my Windows logon account =MyDomain\Administrator to the logons for the SQL Server and for the =databases then I have for the logon of a database say Northwind =MyDomain\Administrator. I also have BUILTIN\Administrators for logon to =the SQL Server and in the databases.
If I go to Query Analyzer and choose SQL Authentication and type in =MyDomain\Administrator and my Windows 2000 SP3 Administrator password I =cannot connect. But if I choose Windows authentication then the account =which is used to logon to the SQL Server is MyDomain\Administrator.
This is confusing to me. Why when I try to login using Query Analyzer =using SQL authentication and the Login name MyDomain\Administrator I =cannot connect? But if I use Windows authentication the login name =shows as MyDomain\Administrator? This doesn't make sense to me.
It seems to me that all the Users that I have in my Users in Windows =2000 SP3 if I add them to SQL Server as it gives me the capability to, =then I cannot use those accounts to login to SQL 2000 SP3 Server. =Unless I have signed into Windows 2000 using one of those accounts. If =I have not signed into Windows 2000 SP3 using one of those accounts then =they are worthless to sign into SQL 2000 SP3. Thanks.
-- George Hester
__________________________________
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message =news:#57kO8dwDHA.1764@.TK2MSFTNGP10.phx.gbl...
> >>
> Since I don't know what the password is for the IUSR_MachineName =account I
> am assuming I could use TrustedConnection=3D"yes"
> >>
> > No, this won't work... you can't have a trusted connection *and* =specify the
> userid. You will need to do one of the following things:
> > (a) use a SQL Server user in your connection string.
> > (b) if you want to use a trusted connection, you will need to set up =the
> passwords correctly. The IUSR account on your web server is *only* on =your
> web server. The SQL Server machine has no idea who IUSR_webserver is, =so
> even if you add that user to SQL Server, the passwords won't =synchronize.
> In Windows 2000, I believe there was a setting in IIS to disable =automatic
> password synchronization for the IUSR account, and override the =password.
> Otherwise, you will have to change the password in password in
> Administrative Tools / Computer Management / Local Users and Groups / =Users.
> > Once the web server has a password for IUSR_webserver that you know, =you can
> do one of two things:
> > (i) use a connection string with *only* trusted connection=3Dyes (no =userid
> information - see a sample at http://www.aspfaq.com/2126) and make =sure that
> windows authentication is disabled
> > (ii) add the same username/password as a SQL Server user, and include =the
> user id and password in the connection string (and remove the trusted
> connection bit).
> > -- > Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
> >|||On Sat, 13 Dec 2003 20:35:04 -0500, "George Hester"
<hesterloli@.hotmail.com> wrote:
>Hi Aaron. Ok I see it won't work as I was hoping. Too bad I can't just send the IIS generated password to SQL. That would make life so much easier.
>But one thing I am noticing. If I add my Windows logon account MyDomain\Administrator to the logons for the SQL Server and for the databases then I have for the logon of a database say Northwind MyDomain\Administrator. I also have BUILTIN\Administrators for logon to the SQL Server and in the databases.
>If I go to Query Analyzer and choose SQL Authentication and type in MyDomain\Administrator and my Windows 2000 SP3 Administrator password I cannot connect. But if I choose Windows authentication then the account which is used to logon to the SQL Server is MyDomain\Administrator.
>This is confusing to me. Why when I try to login using Query Analyzer using SQL authentication and the Login name MyDomain\Administrator I cannot connect? But if I use Windows authentication the login name shows as MyDomain\Administrator? This doesn't make sense to me.
>It seems to me that all the Users that I have in my Users in Windows 2000 SP3 if I add them to SQL Server as it gives me the capability to, then I cannot use those accounts to login to SQL 2000 SP3 Server. Unless I have signed into Windows 2000 using one of those accounts. If I have not signed into Windows 2000 SP3 using one of those accounts then they are worthless to sign into SQL 2000 SP3. Thanks.
Hi George,
You can achieve what you want if you change the security attributes on
your website to disallow Everyone and the anonymous user, and allow a
specific domain group, where that domain group includes the people you
have given access to SQL Server.
This will make IIS prompt the browser for authentication information. If
your users are using Internet Explorer and are logged into the domain,
then authentication will be transparent, otherwise they will be prompted
to enter their username and password.
They will then be authenticated in IIS as themselves, instead of the
anonymous user, and the secure connection to SQL Server will also be as
themselves.
cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander|||>> This is confusing to me. Why when I try to login using Query Analyzer
using SQL authentication and the Login name MyDomain\Administrator I cannot
connect?
Because this is not a SQL user! This is a Windows user! If you are logged
in as MyDomain\Administrator, use Windows Authentication. Otherwise, use a
SQL user account.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||> You can achieve what you want if you change the security attributes on
> your website to disallow Everyone and the anonymous user, and allow a
> specific domain group, where that domain group includes the people you
> have given access to SQL Server.
> This will make IIS prompt the browser for authentication information. If
> your users are using Internet Explorer and are logged into the domain,
> then authentication will be transparent, otherwise they will be prompted
> to enter their username and password.
> They will then be authenticated in IIS as themselves, instead of the
> anonymous user, and the secure connection to SQL Server will also be as
> themselves.
Great addition Ross.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Aha finally. I got it from here:
http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;247931&Product=3D=
sql
To use the IUSR_MachineName account and SQLOLEDB (note the missing .1) =I have to have "Integrated Security=3DSSPI" in the connection string. I =also have to have in SQL 2000 SP3 MyDomain\IUSR_MachineName have =db_datareader and db_datawriter access to the my_logon database. The =account must also have db_datareader and db_datawriter permissions to =the tempdb database. Also the account must be set up with Windows =Authentication which makes sense.
So the connection string is:
strConnect =3D "Provider=3DSQLOLEDB;Integrated Security=3DSSPI;Initial =Catalog=3Dmy_logon;" & _
"Network Library=3Ddbmssocn;Data Source=3DMySQLServer"
Now this article says "You must use one of the following two IIS =authentication methods:"
Actually I wanted to use the first method. Looks easier. But it was =not correct. I removed the Anonymous access and left only Basic =Authentication as the article suggests. I was returned a Domain logon =when I tried to access the web application. That I did not want. Also =the error was the AddHeader was insufficient information. Well that was =easy to fix. Just put back in the Anonymous account whose password is =handled by IIS.
That seems to have done it.
I can connect. Now to just finish with this article:
http://support.microsoft.com/?scid=3Dkb;en-us;299987
whew!!
-- George Hester
__________________________________
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message =news:#57kO8dwDHA.1764@.TK2MSFTNGP10.phx.gbl...
> >>
> Since I don't know what the password is for the IUSR_MachineName =account I
> am assuming I could use TrustedConnection=3D"yes"
> >>
> > No, this won't work... you can't have a trusted connection *and* =specify the
> userid. You will need to do one of the following things:
> > (a) use a SQL Server user in your connection string.
> > (b) if you want to use a trusted connection, you will need to set up =the
> passwords correctly. The IUSR account on your web server is *only* on =your
> web server. The SQL Server machine has no idea who IUSR_webserver is, =so
> even if you add that user to SQL Server, the passwords won't =synchronize.
> In Windows 2000, I believe there was a setting in IIS to disable =automatic
> password synchronization for the IUSR account, and override the =password.
> Otherwise, you will have to change the password in password in
> Administrative Tools / Computer Management / Local Users and Groups / =Users.
> > Once the web server has a password for IUSR_webserver that you know, =you can
> do one of two things:
> > (i) use a connection string with *only* trusted connection=3Dyes (no =userid
> information - see a sample at http://www.aspfaq.com/2126) and make =sure that
> windows authentication is disabled
> > (ii) add the same username/password as a SQL Server user, and include =the
> user id and password in the connection string (and remove the trusted
> connection bit).
> > -- > Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
> >|||Well I don't see why. SQL Manager clearly shows that =MyDomain\Administrator has login permissions. Well never mind. I got =it fixed. Thanks anyway.
-- George Hester
__________________________________
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message =news:ezC#ycewDHA.1756@.TK2MSFTNGP09.phx.gbl...
> >> This is confusing to me. Why when I try to login using Query =Analyzer
> using SQL authentication and the Login name MyDomain\Administrator I =cannot
> connect?
> > Because this is not a SQL user! This is a Windows user! If you are =logged
> in as MyDomain\Administrator, use Windows Authentication. Otherwise, =use a
> SQL user account.
> > -- > Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
> >|||>> I have to have "Integrated Security=SSPI" in the connection string.
Yep, in my first reply:
http://www.aspfaq.com/2126
You can see this:
<snip>
Using Windows Authentication:
<%
cst = "Provider=SQLOLEDB.1;Data Source=<server/ip>;" & _
"Initial Catalog=<dbname>;Integrated Security=SSPI"
set conn = CreateObject("ADODB.Connection")
conn.open cst
%>
</snip>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment