March 20, 2008 at 5:19 am
I am attempting to create a Web Based Front End for a SQL 2005 database using MS Visual Web Developer 2008.
I was hoping many of you would have already achieved this and would be able to help.
I have created a Web Site on Web Server running IIS 6.0.
I created the aspnetdb on SQL Server 2005 using aspnet_regsql.exe
I have created the Web Site in VWD. I added a few pages. I added a Login control to one of the pages.
I modified the web.config file as follows:
<add name="aspnetdbConnectionString" connectionString="Data Source=SQL-SERVER;Initial Catalog=aspnetdb;User=User;Password=Password;Integrated Security=True"
providerName="System.Data.SqlClient" />
If I open the Login.aspx page in a browser (previews OK). If I eneter credentials I should get directed to 'CreateAccount.aspx. However on entering details I receive error:
Login failed for user 'DOMAIN\WEB-SERVER$'.
I am unable to create users on aspnetdb 'Security'?? Receive error: 'username' is not a valid login or you do not have permission (Microsoft SQL Server, Error: 15007)
I can get it all working locally using SQLEXPRESS.
Anyone got a similar configuration?
Many Thanks,
Phil.
Update: Have resolved the 15007 error or should I say I know how to, not sure what permissions I should be setting!
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 20, 2008 at 5:33 am
go to the Start -> Program Files -> Microsoft SQL Server 2005 -> Configuration Tools -> Surface Area Configuration
and enable relevant properties.
March 20, 2008 at 5:46 am
Hi. SQL Server Configuration is all OK. Server runs a few databases. I have just added aspnetdb as part of the Web Development.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 20, 2008 at 3:08 pm
Locally your credentials probably work using SQL Express. When you go to an IIS situation against a remote server, the IIS account is what is used to connect, not your account that connects to IIS. You can configure IIS to ask for or pass through your credentials, or you can configure IIS to have it's own credentials to use, but that's an IIS configuration item.
March 21, 2008 at 12:31 am
Actually the problem is that you're specifying both SQL Server credentials and Integrated Security - Integrated Security will use the identity of the configured application pool, in your case I suspected removing the "Integrated Security = "true"" from your connection string will take care of the problem assuming that the UID and PWD are valid for connecting to your SQL Server.
March 22, 2008 at 3:56 am
Thanks for the reply's gents. I have gone back to basics (deleted the site). I now have to retrace my steps then implement your suggestions and hopefully get it all working!!
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 22, 2008 at 4:54 am
OK. I re-created the site. My web.config file looks as follows (see attached .txt file).
The error I receive is as follows:
The user instance login flag is not supported on this version of SQL Server. The connection will be closed
Am I close? 🙂
Thanks,
Phil.
PS: To note I created a new application pool in IIS called 'AspNetAppPool' the account used is 'Network Service' and it is this pool that my Web Site uses.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 22, 2008 at 8:44 am
Hi Guys. I have now managed to resolve the Login issue. I had a local SQLEXPRESS installation. I uninstalled this as I read in a post elsewhere that this might be confusing the issue
I created a new Login on the SQL 2005 Server. I mapped the new Login to the database 'aspnetdb' + made it the db_owner (public also selected as this was checked by default). I reference this account in my connection string in the web.config file.
My connection string is now:
<add name="aspnetdbConnectionString" connectionString="server=SQL-SERVER-NAME;Database=aspnetdb;User Id=User;Password=Password"
providerName="System.Data.SqlClient" />
Is there a way I can get around adding the User Id + Password to the web.config file?
Also any advice regards Database 'Roles' in SQL Server 2005 + 'ASP Membership' & 'ASP Roles'?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 22, 2008 at 9:05 am
Is this a shared app? You can set IIS to send through a particular set of credentials (like a service account) for anonymous users. Then you can add this as the login in SQL Server.
You shouldn't assign db_owner if it's not needed. Create a user, create a role, add the user to the role, and add the rights to the role.
March 22, 2008 at 10:08 am
Hi Phillip,
I am a little late but the ASPNETDB comes with default connection string, in the future the aspnet_regsql and aspnet_regiis utilities are in the location below in your c drive. The first two links will show you how to create membership without code. The last link shows how to create roles but these are not SQL Server roles rather application roles for your users. I am assuming you know IIS6 comes locked down to render only static html you have to click on details during installation to add all the components needed to run Asp.net. One more thing Asp.net 3.0 will tartget 2.0 in deployment the references you need will be added by VWD 2008.
http://www.asp.net/learn/videos/video-148.aspx
http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
Easy tutorial to add roles.
http://weblogs.asp.net/scottgu/archive/2005/10/18/427754.aspx%5B/url%5D
Kind regards,
Gift Peddie
March 26, 2008 at 10:33 am
Thanks guys. Steve I need to read up on Roles etc...........If possible I would like to remove db_owner as suggested.
Steve do you refer to the ApplicationPool Identity regards IIS sending through a particular set of credentials? I tried replacing 'network Service' with a SQL Account, when I launched the Web Page I received 'Service Unavailable' message? So I guess that is wrong!! 🙂
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 26, 2008 at 8:54 pm
If you go into the properties for IIS, there's a place where you set security. You can set anonymous users with credentials, which is where you can input a Windows account that IIS will use to access your database server.
Or you can set it to ask for Windows credentials when people connect to IIS.
Or you get just set a name and password in your code that connects to SQL Server using non-Trusted credentials.
March 27, 2008 at 12:16 pm
Thanks Steve.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
April 15, 2008 at 10:49 pm
Philip Horan (3/22/2008)
Hi Guys. I have now managed to resolve the Login issue. I had a local SQLEXPRESS installation. I uninstalled this as I read in a post elsewhere that this might be confusing the issueI created a new Login on the SQL 2005 Server. I mapped the new Login to the database 'aspnetdb' + made it the db_owner (public also selected as this was checked by default). I reference this account in my connection string in the web.config file.
My connection string is now:
<add name="aspnetdbConnectionString" connectionString="server=SQL-SERVER-NAME;Database=aspnetdb;User Id=User;Password=Password"
providerName="System.Data.SqlClient" />
Is there a way I can get around adding the User Id + Password to the web.config file?
Also any advice regards Database 'Roles' in SQL Server 2005 + 'ASP Membership' & 'ASP Roles'?
Many Thanks,
Phil.
I just happened by and noticed this, so I thought I'd add in my 2 cents.
First off, the connection string. I keep a function in the app_code folder called getconnected(). When I need to get data for any given object, I just use that as my connection string. The reason: I can have it change dynamically. For example, I have it check the URL. If the live site is on MyLiveSite.com and I have a development site on Dev.MyLiveSite.com, I can drop the files back and forth and the live site will hit the live db and the dev site will hit the dev db without a code change. If you want, you can even hide the passwords in the registry and dig them out using the function, but I just type them in.
Next, "Users and Roles". I'm not sure what your front end is for, but I'm guessing it is some kind of web interface to a custom database. I never use windows security. Sometimes I need to pick up the pieces and slap everything together on a new box while putting out fires left and right. Each database gets a single username that the website uses. Simple read-write access. In the database's private sections, I keep a separate table of users, just like any other database table. I took that <asp:login object and tossed it.
Anyway, that's just some suggestions. I've been at this for about 10yrs now and these couple features have saved me a ton of time and money over the years.
April 16, 2008 at 6:33 am
Thanks Dan. I am a novice programmer so not too hot at writing my own functions at this time!
The site is to enable users (internally) to view data from our live system relative to their job role / department (i.e. if users belong to sales department they can not view accounts section of intranet site).
My connection string currently uses a single account (same as the one the app uses to connect to sql) to pull out live data, not sure regards the least amount of privileges I can assign to remain functional. I could create a new user to use for the web.config file (not sure regards implementing the function in the app_code folder). Sound pretty good however. How does it update dynamically based on the web site in use?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply