Connecting to SQL Server DB

  • Thread starter Thread starter Aaron Bertrand - MVP
  • Start date Start date
A

Aaron Bertrand - MVP

DSN=SQLServer1;

How is this DSN=SQLServer1 configured? Does it use the name "localhost" or
does it use an actual computer name, or IP address? Are you sure it's set
for port 1433?
 
Hi all,

I'm missing something, probably stupid, on connecting to a SQL Server
database from an aspx file. I haven't really done much w/ SQL Server and
suspect that it's a problem on that side. I've installed the SQL Server
developer edition locally and do have a pubs database.

The connection I'm attempting is the following:

SqlConnection objConnection =
new SqlConnection( "server=localhost;database=pub;uid=sa;pwd=admin" );

try
{
// Open the connection.
objConnection.Open();
/*snip*/
}
catch( Exception ex )
{
ErrorMessage.Text = ex.Message.ToString();
}

The exception caught indicates (whether or not 'sa' is the uid I'm trying to
connect with):
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server
connection. (w/Windows-only Authentication) or
Login failed for user 'sa'. (Windows and SQL Server Authentication)

I have been able to create a connection through VFP (though using ODBC).
The connection string it creates is:
DSN=SQLServer1;UID=sa;PWD=admin;APP=Microsoft Visual FoxPro;WSID=J2K, which
indicates to me that the sa/admin login is correct (which is what Iset at
installation).

Any ideas?

TIA,

John
 
Hello,
I had some similar issues.
1. Include "Trusted_Connection=Yes" in the connection
string (don't forget the semicolon separating elements)
2. sa should be a good to go userid but is a bad idea
for production. In enterprise manager, add a user for
each login ID to use the app then assign the user roles.

The first step should make it work. The second step,
while not absolutely technically necessary, will help to
secure your app. Also, if you use Integrated Windows
Security (or authentication) you will not have to
maintain a user id or password in an open text string.

Mklapp
 
Cross-posting is bad.

Is "localhost" and "SQLServer1" actually the same machine? If not, is your
authentication on localhost "windows only"? Try some these connection
strings

"server=yourIPaddressHere;database=pubs;uid=sa;pwd=admin;"
"server=yourIPaddressHere;database=pubs;integrated security=sspi;"
"server=localhost;database=pubs;integrated security=sspi;"
"server=SQLServer1;database=pubs;integrated security=sspi;"
"server=(local);database=pubs;uid=sa;pwd=admin;"
"server=(local);database=pubs;integrated security=sspi;"
"server=.;database=pubs;uid=sa;pwd=admin;"
"server=.;database=pubs;integrated security=sspi;"

hth
Eric

John Spiegel said:
Hi all,

I'm missing something, probably stupid, on connecting to a SQL Server
database from an aspx file. I haven't really done much w/ SQL Server and
suspect that it's a problem on that side. I've installed the SQL Server
developer edition locally and do have a pubs database.

The connection I'm attempting is the following:

SqlConnection objConnection =
new SqlConnection(
Click to expand...
"server=localhost;database=pub;uid=sa;pwd=admin" );
 
Thanks, Aaron. And sorry in advance for my newbie ignorance...

The DSN is a system DSN, configured as connecting to server "J2K" (which is
the name of the computer as well as of the SQL Server), using the TCP/IP
network library set to dynamically determine port (but, yes, the default
port is 1433). I can't find any reference to localhost in the DSN.

BTW, I don't think the ADO connection is attempting to use the DSN (but
could be COMPLETELY misinterpreting things).

- John

The default port is 1433.
 
The DSN is a system DSN, configured as connecting to server "J2K"

So try

new SqlConnection( "server=J2K;database=pub;uid=sa;pwd=admin" );

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/





(which is
 
Hi,

If it's SQL2K, then you've probably not got the server in Mixed mode. Go
into enterprise manager and right click on the server (should be the same
name as the computer unless you've changed it) and go into the properties
page.

Go to the security page and you'll find an Authentication option. Select
SQL server and Windows. Important note here...You must make sure you change
the SA password to a nice long password with lots of _£$@} characters in,
otherwise, if you have broadband you'll be hacked to pieces.

You need to have the database server in mixed mode if you want to use forms
authentication (sounds like you do). Otherwise you might want to consider
integrated security, in which case you don't use uid and pwd in your
connection string and you ignore everything I've written above.

Have fun....welcome to the DBA's world!

P


John Spiegel said:
Hi all,

I'm missing something, probably stupid, on connecting to a SQL Server
database from an aspx file. I haven't really done much w/ SQL Server and
suspect that it's a problem on that side. I've installed the SQL Server
developer edition locally and do have a pubs database.

The connection I'm attempting is the following:

SqlConnection objConnection =
new SqlConnection(
Click to expand...
"server=localhost;database=pub;uid=sa;pwd=admin" );
 
Thanks to all of you (Aaron, Mklapp, Eric & Paul) for jumping in. So far,
I've still had no luck.

This is a list of the connections I've tried so far:

server=localhost;database=pubs;uid=jspiegel;pwd=abc;
server=J2K;database=pubs;uid=jspiegel;pwd=abc;
server=J2K;database=pubs;uid=sa;pwd=admin;Trusted_Connection=Yes
server=localhost;database=pubs;uid=sa;pwd=admin;Trusted_Connection=Yes
server=localhost;database=pubs;uid=sa;pwd=admin
server=J2K;database=pubs;uid=sa;pwd=admin
server=localhost;database=pubs;Trusted_Connection=Yes
server=<IPaddress>;database=pubs;uid=sa;pwd=admin;
server=<IPaddress>;database=pubs;integrated security=sspi;
server=localhost;database=pubs;integrated security=sspi;
server=SQLServer1;database=pubs;integrated security=sspi;
server=(local);database=pubs;uid=sa;pwd=admin;
server=(local);database=pubs;integrated security=sspi;
server=.;database=pubs;uid=sa;pwd=admin;
server=.;database=pubs;integrated security=sspi;

The uid=jspiegel;pwd=abc correspond both to a domain login and a user I've
added to SQL Server.

While I am logged into the domain, all of this is happening (or trying to
happen) on my local machine (Win2K Pro, SQL Server 2000 and .NET framework
1.1).

While I have toggled it a few times, most of my attempts have been with SQL
Server and Windows set as the authentication.

I had installed MSDE quite a while ago and then the SQL Server trial
version. Yesterday, I tried to wipe the slate clean and reinstall MSSQL2K
fresh. Probably doesn't answer anything but I'm grasping.

Thanks again,

John

BTW, I think I was spoiled years ago working in a single environment with
native DB. This interconnection stuff drives me batty!

John Spiegel said:
Hi all,

I'm missing something, probably stupid, on connecting to a SQL Server
database from an aspx file. I haven't really done much w/ SQL Server and
suspect that it's a problem on that side. I've installed the SQL Server
developer edition locally and do have a pubs database.

The connection I'm attempting is the following:

SqlConnection objConnection =
new SqlConnection(
Click to expand...
"server=localhost;database=pub;uid=sa;pwd=admin" );
 
I don't mind cross-posting so much as multi-posting. However this probably
belonged in one of the following sets:

[microsoft.public.dotnet.framework.adonet,
microsoft.public.sqlserver.connect]

[microsoft.public.dotnet.framework.adonet,
microsoft.public.sqlserver.connect]

[microsoft.public.sqlserver.connect]

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
 
Hey Eric,

Aside from the connection issue...
Cross-posting is bad.
Click to expand...

I've seen this dozens of times (part of why 90+% of the time I don't do it).
But I've never really understood what the problem is--particularly in a case
where there are multiple technologies thrown in the mix. Is it a resource
issue for the newserver or confusion/clutter issue for the readers? Just
trying to understand.

Sorry for being bad,

John
 
You know you're right. It wasn't _that_ bad. Perhaps I just jumped on
something I don't like to see. One of the problems with x-posting is
readers who perhaps are not subscribed to the other groups might not see all
of replies. Likewise, when you are searching on a problem, you might
believe you're seeing incomplete threads hence a solution to an issue was
never found, however the thread could have just continued elsewhere.

Eric
 
something I don't like to see. One of the problems with x-posting is
readers who perhaps are not subscribed to the other groups might not see all
of replies.
Click to expand...

I bet this is rare. Usually, cross-posted groups are dropped when a user
finds that one or more of the groups was off-topic. So, a user who
frequents the off-topic group but not the on-topic group, would be unlikely
to be interested in the thread if it went solely to off-topic groups they
don't subscribe to.
 
My thanks and apologies to all. In a "didn't see the forest through the
trees" kind of way, I eventually realized that the downloaded code I was
using was using the compiled version of codebehind file and not the
on-the-fly changes I was making in the .cs file. Sheesh!

Thanks again for getting involved (and I learned some stuff in the process!)

- John

John Spiegel said:
Hi all,

I'm missing something, probably stupid, on connecting to a SQL Server
database from an aspx file. I haven't really done much w/ SQL Server and
suspect that it's a problem on that side. I've installed the SQL Server
developer edition locally and do have a pubs database.

The connection I'm attempting is the following:

SqlConnection objConnection =
new SqlConnection(
Click to expand...
"server=localhost;database=pub;uid=sa;pwd=admin" );
 
Back
Top