sql report manager: string

Showing posts with label string. Show all posts

Thursday, March 22, 2012

Can't seem to get ASP and SQL2000 to recognize the User IUSR_MachineName

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>

Thursday, March 8, 2012

cant retrieve connection string from web.config - ASP.NET 2.0

All,

I don't know what is the problem, i am not able to retrieve connection string from web.config in my code behind page

I used every solution provided by people in differents forums.

can anybody help me with this.

I used

WebConfigurationManager.OpenWebConfiguration("/TimeSheetApplication").ConnectionStrings("TimeSheetConnectionString").ConnectionString;

WebConfigurationManager.ConnectionStrings("TConnectionString").ConnectionString;

Here is a reference on MSDN

http://msdn2.microsoft.com/en-us/library/system.web.configuration.webconfigurationmanager(VS.80).aspx

or you can post your web.config with above code and let me look

|||

My code

Web.Config -

<addname="TConnectionString"connectionString="Data Source=Source name"

providerName="System.Data.SqlClient" />

</connectionStrings>

Code Behind -

string query ="Select * from table"

string myConnectionString = ??

SqlConnection myConnection =newSqlConnection(myConnectionString);

SqlDataAdapter ad =newSqlDataAdapter(query, myConnection);

DataSet ds =newDataSet();

ad.Fill(ds,"table");

GridView1.DataSource = ds;

GridView1.DataBind();

? - When i used

ConnectionStringSettingsCollection connectionStrings =WebConfigurationManager.ConnectionStringsas ConnectionStringSettingsCollection;

connectionStrings["TConnectionString"].Name

it gives error

Cannot apply indexing with [] to an expression of type 'ConnectionStringSettingsCollection'

The type 'System.Configuration.ConnectionStringSettingsCollection' is defined in an assembly that is not referenced. You must add a reference to assembly 'System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.

|||

You can access this way

string connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["TConnectionString"].ToString();

|||

Thanks Farooq...

However i used the same code earlier and i am getting the error

Cannot apply indexing with [] to an expression of type 'ConnectionStringSettingsCollection'

|||

make sure you have this element in web.config:

<system.web>

</compilation>

</system.web>

Sunday, February 19, 2012

cant login my database

hi,

this is the connection string i am using

'Data Source=.\SQLEXPRESS;INITIAL CATALOG=;AttachDbFilename='|DataDirectory|\HotStage.mdf';Integrated Security=True;User Instance=True '

it works all right when working in debug mode, but after i deploy it to my XP's IIS, i can't login my database, the error message is

'Can not open user default database. Login failed. Login failed for user xxx\ASPNET' 'xxx' is my computer name

i am the Admin. of my computer.

could anybody help ? thx!!!

When running in debug mode, I'd guess that you're using the VS development web server, which runs under your user credentials. Consequently, because you're an admin it is allowed to access SQL Server.

When hosting it in IIS, the worker process is running under the ASPNET user account, and this will not be allowed to access SQL Server by default. Therefore, using SQL Server Management Studio, create a login for the ASPNET account on the SQL Server and then grant it access to the appropriate databases.

|||

hi,

of course my SQL Server has ASPNET account, and it has the permission to 'connect sql' and 'create any database', but i still connect to my SQL.

i read some posts somewhere else, and they said i should remove the 'User Instance=True' clause, and i did connected to my SQL, but now i have another problem...it says my database is read-only

there are two functions on my page, both of which will update the database, but one works all right, the other does not...

|||

Hi,

Could you give us a further explaination on your issue. Do you mean that you remove the 'User Instance=True' clause and connect to your SQLServer succesfully, then fail to use one of your function to update your database while another update function works well? If so ,I think the problem should not caused by the database. Could you give us your code?

Thanks.

|||

hi,

the code is a little bit long, and i really don't know what caused this problem, so i do not know which part got wrong. and i don't think you want to read all my code.

but i can do some further explain, when i remove the 'User Instance=True', and use the 'AttachDB' clause, the database connected dynamically, but in SQL Management Studio, i see a 'Read-Only' label after my database, and my database is displayed in gray.

so would you tell me why my database is connected as read-only? and how can i solve this problem?

|||

Hi,

You need Read/Write permission on both MDF and LDF in order to editing data. If you're using IIS 6.0, you need to add Network Process account and give Read/Write permission to these two files. You also need to detach the database before changing the permissions on the file.

You can do this using SSEUtil.exe tool:http://www.microsoft.com/downloads/details.aspx?FamilyID=FA87E828-173F-472E-A85C-27ED01CF6B02&displaylang=en

Thanks.

|||

hi,

all solved, thanks man!

Cant' log in anymore

Hi,
I pulled down all the files from my live
database which is running remotely.
When I did this, I overwrote my Web Page
connection string, which I used to log into a
local production instance of this database.
After a few weeks I got around to recreating
my connection string, but it won't work.
Neither does my partner's login info work and
we are both admins. I am the dbo. Now I am
wondering if coincidentally somehow my machine
was compromised. Is there someway I could be
kept out? I can still log onto the machine/server
which runs SQL Server for access to the drives.
I am running Windows/2000
and SQL 2000. My authentication
is set to Windows only. I run my network
as a workgroup and there is no "Active Directory".
Logging into the server to access the drives is
no problem. The login is of course the same, since
I am using Windows only authentication.
Is there anyplace I can look to see if there
is some way to get this working again?
It is not making sense to me that I can
log in to the HDs on the LAN but cannot
log into SQL databases via a webpage.
The folders are in folder needed a Windows
login, but the SQL server logs just keep showing
that access is denied because the login is not
a trusted login, which is not true, but I cannot
prove that to the server.
Could I have been compromised or does
this sound like some other confusion?
Thanks for anyone's feedback,
FoxIf the authentication is set to Windows Authentication and you're not in a
domain, then you're basically using Workgroup security to connect. The
same username and password must exists on both machines.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Friday, February 10, 2012

Can't Get SQL Authentication to work With XP Pro SP2 in very simple test case

I'm trying to work my way through the steps of using a User Id and Password in a connection string.

I'm working with SQL 2005 Express, VS2005, in the development server. Got an error I can't get around...tried it several diffent ways on a slightly more complicated test site...no joy...so went to the MSDN tutorial...made the most "vanilla" test I could think of, and still can't figure it out.

I thought it would be simple enough that I could post the whole thing (below)

The test works fine with Integrated Security = True in the connection string. When I remove that phrase, I get the error:

{"CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file E:\MyPath\App_Data\VSST_DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."}

This occurs on the cn.Open statement below.

It gets past the login, so I know that the SQL User and password match up correctly.

==========================
<connectionStrings>
<add name="VSST_CN"
connectionString="Data Source=MyServer\SQLEXPRESS;AttachDbFilename=E:\MyPath\App_Data\VSST_DB.mdf;User Id = VSST; Password=vsst123"
providerName="System.Data.SqlClient"/>
</connectionStrings>

=========================
Page Code Behind (no controls on page)

Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("VSST_CN").ToString())

Dim cmd As SqlCommand = New SqlCommand("SELECT COUNT(*) FROM VSST_Table", cn)
cn.Open()
Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
rdr.Read()
Response.Write(rdr(0).ToString())

End Sub
End Class

=================
The DB

Table VSST_Table,
ID is int, primary key, identity
Field1, Field2, Field3, Field4, Field5 are varchar(50)

I added one record ("A", "B", "C", "D", "E") to the table through VS2005 Server Explorer

This shows up in Count = 1 from running the page when Integrated Security = True

=================
In SSMSE: (this is ALL I did, tried to use the minimum so not to confuse...)

I added the SQL Authentication Server level user "VSST" with the password "vsst123" (and the login works, as noted above)

I attach the .mdf

I add VSST to the Database Users, and give it db_owner

I add VSST to the Table with all permissions checked.

=====================================

I can't figure this out. This is a very vanilla test and I'm stumped. I'm about to give up on SQL Authentication entirely (at least for now), and just try to filter my inputs for SQL Injections...that's the only reason I have (at this stage in my biz plan) for needing SQL Authentication. On the other hand, I really don't like being this stumped on something that is so widely promoted as a common practice.

Any help on this would be greatly appreciated.

Thanks!

It's an XP SP2 security issue. The connect is running within an application sandbox which is not granted the authority to attach, create, or restore a database. The integrated security scenario works, because the Windows credentials are passed through. We spent days trying to figure a way around this one when dealing with a click once application installation and finally gave up. There wasn't a single permutation that we could use to get around the security restrictions that XP SP2 put in place. If the database already existed, everything connected and worked just fine. But, it simply refused to attach a database.|||

Thanks for the input. Wow. Good to know I wasn't completely blowing it....it looked too easy.

So, maybe it will work on the Windows 2003 VPS Server I'm renting...I guess I'll have to give it a try. But maybe it tells me I need to consider why SQL Authentication is critical to this app.

Grazie!

|||

I think there was something in the decision tree that I missed...

As I am gradually putting this together, it looks like the first choice is "1 server => windows authentication" vs "multiple servers (plus some other cases) => sql authentication"

After that, everything that is written about sql authentication seems to assume a level of professional expertise which is appropriately associated with a multi-server environment. Which probably means that the developers may have XP on their desks, but that they're logging into an W2003 development environment...so they never get exposed to this problem (or are aware of it and bypass it).

If you miss the first branch in the decision tree, this is never mentioned...it's all about encrypting web.config and guarding against sql injection by limiting table access, clever ways to use trusted this and that....but never "maybe you should just use Windows Authentication, if it's a single server app."

Long way to the first u-turn....hazards of being a newbit!

|||

Similar grief here

http://www.mcse.ms/message2347651.html

Can't Get SQL Authentication to work in very simple test case