connection to sql server

S

son

Regular
Joined
Feb 21, 2004
Messages
67
hi hope all is well... i am using vb.net as my language and asp.net. I am using sql server 2000 as my database..

i am getting really confused in re: how to make a connection from my web application to my sql server.. i am using the northwind database... and using everything standalone on my pc.

my sql service manager when i start sql has as my SERVER: is SONIA

i went into the webcongfig file and in the tags of <appSettings></appSettings>

Visual Basic: 
<appSettings>

<add key="" value="user id=sa;password=;initial catalog=Northwind;data source=;Connect Timeout=60;max pool size=400"/>
		<add key="onlineVersion" value="3.02"/>
</appSettings>


can someone tell what i have to type in the key="", source="" and if there is anything else i have to add.

also i want to set the connection in a module which will be global and then call it in the aspx.vb pages..


thanks in advance ..sonia
 
The web.config is really just a convenience, it prevents you from having to hard code things like connection strings into the database. You can use any value for the key - just select something that makes sense.
Once you have defined a key in the web.config you can then use it in your code via the AppSettingsReader class.
e.g. given the following web.config entry
Code: 
<appSettings>
<add key="NorthwindConnection" value="user id=sa;password=;initial catalog=Northwind;data source=;Connect Timeout=60;max pool size=400"/>
</appSettings>

the code to access and use it would be something like
Visual Basic: 
Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
dim conn as = New System.Data.SqlClient.SqlConnection
conn.ConnectionString = CType(configurationAppSettings.GetValue("NorthwindConnection", GetType(System.String)), String)
conn.Open
'now use connection as per normal
this means an administrator could change the connection string by simply editing the web.config without having to recompile/ restart the web application.
 
Last edited:
thanks...

heya there thanks loads for your help me really appreciate it.. take care sonia.. :)
 
i keep on getting this error when i am trying to run my web form

Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection

can someone please help.. thanks sonia....
 
To use the sa account SQL server needs to be setup to use what is know as 'Mixed Mode' security.
If you start enterprise manager and right click on your server and select properties, then go to the security tab. You will need to select the option labeled 'SQL Server and Windows'. Then press OK and restart the SQL Server service.
Having a blank sa password is highly insecure and a major security risk

Rather than using SQL security you may be better of using integrated security. To do this you will need to create a login in SQL for the ASPNET account (enterprise manager, security, logins) and modify your connection string to be
Code: 
"Integrated Security=true;initial catalog=Northwind;data source=;Connect Timeout=60;max pool size=400"
that should fix the problem, if not let me know what errors are generated.
 
Last edited:
hi thanks for your reply & helping me out...

i went on logins and added a new user i gave the name : sonia and i gave a password and database : northwind and language: english (on the general tab) then on the (server roles tab) i selected all that are in the server roles then on the (data access tab) i choose northwind with sonia as the user and for the database roles i choose all.

then in the webconfig file i have

Visual Basic: 
<add key="NorthwindConnection" value="user id=sonia;password=;Integrated Security=true;initial catalog=Northwind;data source=;Connect Timeout=60;max pool size=400"/>

i specified my password...

but still i get the error Login failed for user 'SONIA\ASPNET'.

my operating system is windows 2000 server professional on my pc..

thanks sonia
 
You need to provide either a user id / password or the integrated security = true, not both. Also is the server set to allow SQL logins (see my above post)?
 
Back
Top