SQL Connection and ASP.NET

Diddy Kong

hey, i recently moved some databases to sql from access and am in the middle of updating the connection strings in asp.net, but i get the following error

System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.

but it does exist and it is the right password (i can connect and view data through excel) heres my connection string

Dim strConn as string = "SERVER=server001;DATABASE=database001;UID=USER001;PWD=USER001;"

any help much appreciated

Spunj

Connection strings need to be specified a little differently for SQL server.

Heres the kind of way it should look:
"Data Source=server001;Initial Catalog=database001;User ID=USER001;Password=USER001;"

The best tip I ever got for building connection strings for .NET is to right click on the desktop>New Text Document, then rename it to something like test.UDL.

Once renamed you can right click on it>Properties to set the desired properties. when they are all set, open the file in Notepad and copy out your correctly configured connection string.

Diddy Kong

just tried that, but i still seem to get the same error, any other suggestions??

Evil Phil

Are you connecting to a local instance of SQL Server or a remote instance? Oh, and where are you storing the connection string?

Diddy Kong

remote instance...

BrownBoy

Can u post the complete code sequence from declaring connection string to opening connection ? Is the SQL server running on default port (1443) ? If not you need to specify the port number too.

Evil Phil

You may need to use the IP address and port as the Data Source parameter. Have a look at this .

Additional: Although I've just had a look at my own remote connection strings and I'm not using this so it mightn't work.

Diddy Kong

Dim strConn as string = "Persist Security Info=False;User ID=USER001;PWD=USER001;Initial Catalog=Database001;Data Source=Server001"
Dim myConn as New SQLConnection(strConn)
Dim sql as string = "select stuff from table"
Dim objDR as SQLDataReader
Dim cmd as new SQLCommand(sql, myConn)
myConn.Open

port is 3180

Diddy Kong

thanks BrownBoy, never thought of the port..works now

cheers
dave

RicardoSmith

Do you need the port number on a lan? I don't remember using it myself...

Evil Phil

I'd imagine if you're not using the default then you'd have to specify the new port number, I don't use port number either but we're using the default.

kayos

You can always just setup the client machines connection properties for a SQL Server to default to a different port number. You do it from the "Client Network Utility" that gets installed with the SQL Server client tools. With out that tool I think you can do it from your ODBC manager me thinks...

Diddy Kong

only problem with setting up the connection ODBC manager is that its a web application, so anyone to use it would have to set the connection up, right? anyway its workin, im happy...

Evil Phil

You'd only have to set up the DSN on the webserver (unless you had you data access layer somewhere else) as it would be the application that was accessing the database, not the clients.

kayos

Originally posted by dbfarrell
only problem with setting up the connection ODBC manager is that its a web application, so anyone to use it would have to set the connection up, right?

Huh? Are you doing your database connectivity in a client side script or something?? If its a run of the mill Web App only your Web Server will need to have it configured, if its a n-tier app with an application server then your application server is all that needs setting up.. I have yet to hear of any web app where the client needs to set-up the database connection.

The only piece of advice I want to give you is keep the connection string outside of the code. Your going to have to recompile that project if you need to change the connection string. Also if someone manages to reverse engineer your code they have just got your User name and password for the DB. Use Integrated Windows Security on the DB and get your DAL to run under a NT account with DB access. There will be no need to keep passwords in plain text then!