ASP.NET Connection to SQL server 2000 on local machine
-
Now I'm a dba beginner I've used access and mysql, and only just started using sql server 2000. Now I'm coming to the point where I'm ripping my hair out.....trying to get a connection from asp.net when the aspx page is running. I'm using vs.net 2003, when I setup the sqlconnection object I specific the server, the login method and database and test the connection and it says it works fine however when I run the application it says my login has failed. Here is the error I got:
Login failed for user 'BRENDEN\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'BRENDEN\ASPNET'.
Source Error:
Line 146: 'Put user code to initialize the page hereLine 147:Line 148: SqlConnectionlogin.Open()Line 149: If SqlConnectionlogin.State = ConnectionState.Open ThenLine 150: lblconnect.Text = "connection is fucking Open"
Source File: g:\inetpub\wwwroot\NewsSite\login.aspx.vb Line: 148
Stack Trace:
[SqlException: Login failed for user 'BRENDEN\ASPNET'.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) System.Data.SqlClient.SqlConnection.Open() NewsSite.login1.Page_Load(Object sender, EventArgs e) in g:\inetpub\wwwroot\NewsSite\login.aspx.vb:148 System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive() System.Web.UI.Page.ProcessRequestMain()
Right so I opened up enterprise manager go to SQL Server configuration properties and make sure its both Windows and sql server authenicated, go to the database users and have tried using both a windows authenicated user and sql server user (specifying a username and password) and its still saying login has failed....its starting to drive me up the wall
ANY IDEAS? of what the problem could be???????oh yeah my connection string is:
workstation id=BRENDEN;packet size=4096;integrated security=SSPI;data source=BRENDEN;persist security info=False;initial catalog=CompanyNews
Can't see anything wrong with it???
Thanks in advance,
Brenden
-
The problem is that the project is not using your login but the .NET (ASPNET) login to try to connect to the SQL Server. I see your computer name is BRENDEN and the default .NET service login is ASPNET. What you really want to do is login to SQL Server as yourself (i.e. domain\username).
Your problem is probably in the web.config file for your .NET project. Find the line in the web.config file that looks like the following:
<authentication mode="Windows" />
Right after that line, insert the following line:
<identity impersonate="true" />
This tells your project to actually use your login.
Let me know if that works.
Good luck.
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011 -
Hawq's solution is a good fix, and a quick one, but you don't have to impersonate if you don't want to. You can instead continue to use anonymous, which makes your application run as ASPNET. Hawq is correct, your error is because ASPNET does not have a logon to the database, and there are several workarounds for this.
It is worth noting that I definately would NOT allow ASPNET to impersonate as yourself if you are a developer and have any kind of privilege on your system. This is a large security risk, number 1, and, number 2, it makes moving applications between servers a little harder. You can instead make ASPNET a login for your database, and assign it privileges only to the objects it needs to access.
My applications are lucky enough to only have to allow ASPNET execute access to a subset of the stored procedures in various databases. There is therefore no risk of a rogue user, running as ASPNET, deleting records they are not meant to get access to, or anyone hacking the system as the impersonation account and gaining access to nasty things like xp_cmdshell on the database server - that could be disastrous. Never set up ASPNET as the dbo, it might seem like an easy way to give ASPNET the access it needs, but it violates the principle of least access in so many ways it doesn't bear thinking about.
Probably the best way to do this is:
1) Implement role based security in your database. The simplest method is to create a role called rlASP (or similar). You then use this to manage access to the database objects for a user that fits into the role that ASPNET does.
2) Give BRENDEN\ASPNET a logon to the database server, and to the database that you want it to access - nothing else. Make the logon a public logon, and also put it into your rlASP role. This role can be carried around anywhere you move your database, and will mean you don't have to reassign all the object rights if you change to/from impersonation in the database, or in other scenarios.
3) That's all there is to it, now it works! If you want to give ASPNET access to all the sprocs that you create in one fell swoop, you can script that easily. For example, assuming that your sprocs start with "pr", or something that is unlike the SQL "sp" prefix you could run the following script, in text mode, in query analyser, from the database with rlASP in it.
select 'GRANT EXECUTE ON ' + [name] + ' TO rlASP'
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsProcedure') = 1
and substring([name], 1, 2) = 'pr'
order by [name]
This creates a bunch of grant execute commands that give rlASP access to all your sps that you created, there are lots of ways to change the WHERE to give you other filters on the objects in your database, so you can grant, deny, grant selects on tables instead of sprocs etc. etc..
Now copy the output back to query analyser editor pane, remove any sps you don't want ASP to be able to run, and then run the script. Now, if ASPNET is a member of rlASP, it can go ahead and use it. Better still, if you have any internal windows forms applications that do the same things as ASP, they can log in with a user account which is also a member of rlASP and get the same rights.
If you want to script adding the role, and the user to the role you could do something like:
EXEC sp_addrole rlASP
EXEC sp_addrolemember rlASP, 'BRENDEN\ASPNET'
If this seems complex, I apologise. Go for it now, and it will be well worth the effort. Your application will be nice and secure, you can still use SSPI in your connection string so there are no passwords in the code, and the application will be safer and more secure than if you impersonate a user account which has all sorts of rights on the server. It will also set you up for the scenario where you have IIS and SQL running on seperate machines later on, and don't use the ASPNET account to logon to the database - your new account just gets made a member of rlASP.
Good luck.
Andy
-
Thanks guys for your sugguestions,
But I' managed to sort it, set up a sql server login, changed the connection properties so the connection string is simply datasource;initial catalog;user_id;password and found that did the trick. Thanks again for you replys....it turned out to be simple but took me ages to work out
Cheers,
Brenden
-
I would strongly urge you to consider using one of the methods mentioned here because Windows authentication is much stronger and more secure than SQL authentication. Also, using SQL authentication leaves your login and password out in plain text.
Regarding Andy's method, and there is nothing wrong with it, if you use this method you will need to setup SQL Server access for the MachineName\ASPNET account of whichever machine you eventually install the application on. If you ever have to change machines, you will have to add another login.
Also, this method works great if you can make all of your developers use stored procedures. This allows you to set security only on the SPs and not at the table or view level. If, however, your developers insist on using SQL statements in code (and you cannot change that), you will also have to give that table/view level access to the MachineName\ASPNET account(s).
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011 -
I didn't realised Windows authentication was much stronger, I think it would be best to use your method.
Thanks,
Brenden
-
Thanks Hwaq, you are right; for the added security of this method you do have to put in more effort. I didn't make that clear, nor did I make it clear what you have to do to enable the logins on the server.
Brenden; I would never store usernames and password in your code if you can help it, even in connection strings. It is really easy to open a C# or VB.NET assembly that has been compiled and see al the code inside it (see Lutz Roeders reflector tool http://www.aisto.com/roeder/dotnet/).
OT to your original query, but onT to hawq's last post: use the stored procedure method as much as possible. Its a lot easier to control access rights. If you can stop yourself developing on-the-fly SQL in the code, it has two effects. Firstly, it helps prevent SQL injection attacks (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag04/html/InjectionProtection.asp). Secondly, it improves performance. In general, your stored procedures will have an execution plan pre-calculated and cached on the SQL server, so they will run a lot faster than dynamic SQL. If your database changes its data content drastically, and you re-index to improve performance, you can recalculate the exec plans for your sprocs.
If you find you are writing a lot of code to run your SQL queries, sprocs etc. you can write your own helper libraries to perform standard tasks (e.g., retrieving a sproc's parameters, running a sproc, filling a datareader etc). I swear by the MS data application blocks for fast development when you aren't entirely sure how your helper library should be written (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/daab.asp). I am currently using v2, though there are new releases.
Andy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply