Now the member you were helping got their issue resolved but I found this threat in reguard to an issue I had. The comments on this thread look very helpful and I plan on reading the comments over again as I work on things for help.
Now you stated he was making it more complicated than he needed to. I am not totally sure what he was creating but my intent is to use sqlmembership class instead of going to the usercreation wizard route and using profiles to store additional user information. It appeared to me it was not a scalable solution, I want to create something that has the potential to store lots of data per user possibly using multiple dbs, and I need to be able to have access to the username or key from the master db to link the others.
http://msdn2.microsoft.com/en-us/library/ms998317.aspx
I tried to do what microsoft wanted me to do. I hope this article was not on the wrong track but it told me for some reasn to do this...
"
Create a User Store Database
The SQL Server membership provider stores user information in a SQL Server database. You can create your SQL Server user store manually by using Aspnet_regsql.exe from the command line. Alternatively, you can run Aspnet_regsql.exe in Wizard mode or use the ASP.NET Web Site Configuration tool available on theWebsite menu in Visual Studio .NET 2005.
To create the user store database
Use Aspnet_regsql.exe to create the membership database. From a Visual Studio 2005 command prompt, run the following command.
aspnet_regsql -S (local) -E -A m
-S specifies the server, which is(local) in this example.
-E specifies to use Windows authentication to connect to SQL Server.
-A m specifies to add only the membership feature. For simple authentication against a SQL Server user store, only the membership feature is required.
For a complete list of the commands, runAspnet_regsql /?.
"
I was just following the steps in this long article to see if it would get me where I want to get to. I don't mind grunting things out. This command failed for some reason. I hope that when I reread this thread I can figure out where to go from here. Do I really need to do this to use the sqlmembershipprovider class?
I got an error 40 when I ran that command...
"
C:\Program Files\Microsoft Visual Studio 8\VC>Aspnet_regsql -S (local) -E -A m
Start adding the following features:
Membership
............
An error has occurred. Details of the exception:
An error has occurred while establishing a connection to the server. When conne
cting to SQL Server 2005, this failure may be caused by the fact that under the
default settings SQL Server does not allow remote connections. (provider: Named
Pipes Provider, error: 40 - Could not open a connection to SQL Server)
"
Is this just trying to setup the tables automatically and that is it? I have been programming for a long time but am new to asp. Once I have this stuff setup and am able to access the db how I want and use it, I figure the rest is regular programming and less propriatary to asp. I am sure once I get it I will say thats all, but again so is a 3 number locker combination but really hard when you don't know what the numbers are and havn't found them yet.
Thanks
Jim
Seems that your SqlServer is not properly configured.
For this, you need to go to Start-> All Programs -> Microsoft Sql Server 2005 -> Configuration Tools -> Sql Server 2005 Surface Area Configuration -> Surface Area Configuration For Services and Connections . In the left panel select Database Engine -> Remote Connections and enable 'Local and Remote Connections' -> 'Using both TPC/IP and Named Pipes. Click Apply. Select from the left panel Database Engine -> Service and click Stop, wait until it stops and then hit 'Start', to restart the sql server service.
Install the Sql Server Management Studio and try to connect to your sql server using windows authentification. It should work. While still conected to the Sql Server, in the Object Explorer window, expand the treeview as follows: Security -> Logins -> sa . Double click the 'sa' user and in the Status tab enable 'grant permission to connect to database engine' and set Login to 'enabled'. In the same pop-up you can change the password to the 'sa' user. Save the changes. Try to connect using the sa username and password. It should work.
You can find theaspnet_regsqltool in this directory: "c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\". In the same directory are the sql scripts that create the aspnet_db database. They are the only scripts in that directory.
|||
thank you very much for your reply ! :)
You gave very well thought out detailed information. I did have to change the setting to enable login it was not enabled. Now you wanted me to then login with the username sa using my new password for user sa using sql server authentication right ? That failed. I can only login using the windows authentication.
I am very thankful that I finally found the aspnet_regsql "wizard". I wanted to use this just incase any of the command line stuff I was entering though the command prompt was at all in error. Unfortunatly when I ran this program I recieved this error.
"
Setup failed.
Exception:
Unable to connect to SQL Server database.
------------
Details of failure
------------
System.Web.HttpException: Unable to connect to SQL Server database. --> System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString)
-- End of inner exception stack trace --
at System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString)
at System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install)
at System.Web.Management.SqlServices.Install(String database, SqlFeatures features, String connectionString)
at System.Web.Management.ConfirmPanel.Execute()
"
I will say here as well that one of my main goals is to have membership without throwing everything into the app data folder as a profile but put the additional user information into a seperate sql database. I read you have to specify this in the aspnet_regsql "wizard". I just went with the defaults this time and saw no opportunity that I understood where I could specify to put the app data elsewhere. Here is a link to info I was looking at and an article snippet.
"If you want to store the user account information elsewhere - perhaps in a SQL Server 2000 database, or a SQL Server 2005 database not in theApp_Data folder - you'll need to use the ASP.NET SQL Server Registration Tool (aspnet_regsql.exe) tool. This tool has a graphical component or can be used through the command-line. The graphical wizard allows you to specify the location to add the needed tables. For more information on using this tool refer to thetechnical documentation. "
http://aspnet.4guysfromrolla.com/articles/120705-1.aspx#postadlink
Another thing I want to mention is I have had a working example program working from ...
http://weblogs.asp.net/scottgu/archive/2005/10/18/427754.aspx
It uses roles and profiles and an sql database. For this example I simply created the Aspnetdb.mdf in visual studio and I do not remember the entire process but it contained all the nessesary tables. Now when researching the sqlmembershipprovider it is not said anywhere to create the db in this fashion but to use the aspnet_regsql command line or with the wizard. Is there something different happening when I create the db this way? If I have to create the db in this manor so that I can database the profile data in a secondary conventional database then I will create it this way. I simply did not like what I saw in the profile table where i saw one row per user with all the different variables all smoothed into this row. I just felt it would be nice if the profile was stored in a conventional table structure. It did not appear that the profile system was a very scalable solution for situations where there are many users and where each user may have large amounts of data associated with them. If I am missing something or confused about something let me know. I am on the best track that I know to be on at this point.
I will read though your post along with this thread and other things to see if I missed anything or figure some more out. Your post was a very excelent reply though I did not figure out everything I just have many things to figure out yet.
Jim
ps for anyone looking to install Sql Server Management Studio heres is the link...http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796#filelist
|||
There is one more step to configure the Sql Server, this is the one step I usually forget
: In Sql server Management Studio , go to Object Explorer -> Properties ->Security, and enable the "SQL Server and Windows Authentification mode" under the Server Authentification option. Try to login to the Sql server with the sa user. If the login succeedes than you know that your app. can login to the Sql Server using SQL Server Authentication.
Now, about Membership and Profile DB. By default, all the needed tables are created in a separate Db, called aspnet_db. If you run the "ASP.NET Configuration" tool from your web project( VS 2005 ), then the aspnet_db will be created in the App_Data. If you use the aspnet_regsql wizard than, by default, the aspnet_db is created and attached to your SqlServer. If you want to add only the required tables, views and stored procedures to an already existing DB ( let's call it TEST ), then, when you run the wizard, in the "Select the server and database" screen, set the name of the server ( if you are using your local Sql Server Express than it's name is [Local Machine Name]\SQLEXPRESS ), choose a type of authentification and choose the TEST db in the Database dropdown list. If you want even more control on this process, you can study in-depth the sql scripts that are used to create the aspnet_db structure( tables, views, stored procedures ) and run them manually.
The way the profile is stored in the Profile table is quite weird and hard to interogate without using the provided stored procedure. You can change this, to have profile columns in the Profile Table by taking a lookhere and, for implementation,here orhere
|||
Thank You very much for your reply.
The login to the sql still failed. The first time I followed your instructions I did forget one small part but I redid them all again to make sure it was right. (I did make sure to grant the permission and enable the login and change the radio button with the tcp pipes and such you mentioned) One thing though I did not see anything that said "sql server and windows authentification mode" but just a checkbox greyed out if i did not want to allow sql login I thought it said. Maybe I just didn't look in the right place. The sql server management studio does throw an error when I first open it but it seems to work fine otherwise. Could it throw some kind of exception due to some other setting somewhere else screwed up?
ok now the aspnet_bd stored in the App_Data vs it attached to sql server ... are there any preformance differences? if they are both sql files isn't it both very similar? which way will let me upload it to a server on the net and get setup the easiest? I just wanted to make sure that I had the data stored in a normal real sql database and nothing screwy that will cut down preformance or make it difficult for me to interact with it with programming code or sql commands.
The info you provided about using profiles as a table format instead of being serialized is great. I feel like I can get things going now, I want to make sure I have the right db setup and such first too. You have me on the right track now i feel. Otherwise I was thinking about trying to create a new class baised on the createuserwizard class to get things to work how i wanted them but I was not really sure what I would need to put into it to make it work right since I have not seen any of its raw code in how it works etc. I was going to do this to store data with the user data and avoid the profiles all together because I felt the serialization was just not something I wanted to use.
You have unstucked me and i will keep all these links you have given me I am surprised I did not find them myself already I thought I had already found all of them lol.
Jim
|||
To set the Sql Server and Windows Authentification mode:
In Sql Server Management Studio, open Object Browser, connect to your Sql Server, right click on it( it's the root of the tree view displayed in the Object Browser ), select Properties and the "Server Properties" window will open. In the left panel, select Security and then, in the right panel you'll have the Server Authentification mode setting. Select the Sql Server and Windows Authentification mode. OK and OK and you should be now able to connect to the server with the sa user.
The only performance penalty you'll get from the aspnet_db is the one induced by it's stored procedures. More detailshere .There's no need in attaching the aspnet_db to the SQL Server, unless you want to understand, optimize and/or run queries against this db. There is no difference between aspnet_db and a db created from Management Studio. The great thing about providers is that you can include the aspnet_db structure in your database and you can make the authorization, authentification and personalization work by just deriving the needed Sql*Provider class in your code.
|||
When trying to login to the sa using SQL Server Management Studio, I get the error now... Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection (microsoft SQL Server , Error 18452)
I did not look up the error on the web yet though. Now before i did this fix I tried using visual studio to make the db and it would not let me, it gave me the error I had been getting previously in the sql server management studio which I posted before. Now it strikes me as weird because I was able to create a db using visual studio before just fine. I am wondering if the install of the sql server management studio changed a setting or wasnt installed right. There were 2 versions, I chose the non 64 one assuming it ment 64 bit code or something... I am dunning windows xp pro and an non 64 bit cpu... I might try to reinstall it though. When I start it up it mentions something about cant create sub key under volitle parent key and throws an exception and has a bunch of details if I choose to look at it. I will play with it more tomorrow.
I can tell that what you told me to do was needed though I got a different error. I am wondering if somehting more unusual is screwed up on here. My computer runs fine otherwise though.
I will post if I can find a solution to this myself.
I have been lookin at the other links you showed me and they are great.
|||
I uninstalled and reinstalled sql management studio and I no longer get exceptions thrown from the the studio when I open it. I can now login to sa db using sql authentication. Visual studio did not let me create a db though using sql authentication or windows authentication, it gave an error 40. I am going to double check settings and things didnt change and anything else I can think of. I was about a month ago or so able to create a db using I believe windows authentication in visual studio. It seems I can't do that anymore so it confuses me.
Jim
|||
I was able to create a db using add a connection and letting it make the bd using windows authentication, I was not able to create one using sql authentication in that mannor. This is probably how I created my original db. Now if I select create new sql database then neither way will create it.
Why would one work and one not work? Is it creating the same db? It seems that I need to create one using sql authentication anyway if I want to optomize things later , so I guess I will work on trying to get that figured out and the how and why will sort itself out in my mind later.
