Connecting to SQL Server DB

[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?

 

[John Spiegel]

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

 

[Eric Sabine]

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

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" );

 

[John Spiegel]

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.

 

[Aaron Bertrand - MVP]

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

 

[Paul M]

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

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" );

 

[Aaron Bertrand - MVP]

Have you been to www.connectionstrings.com?

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

 

[John Spiegel]

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!

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" );

 

[Aaron Bertrand - MVP]

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/

 

[John Spiegel]

Hey Eric,

Aside from the connection issue...

Cross-posting is bad.

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

 

[John Spiegel]

Heading there now!

Have you been to www.connectionstrings.com?

 

[Eric Sabine]

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

 

[Aaron Bertrand - MVP]

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.

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.

 

[John Spiegel]

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

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" );

 

[len nelson]

It's true, the sqlConnection does not allow you to use an ODBC
connection so you'd either have to not use one, use the slower, less
optimized odbcConnection, OR... just read the ODBC settings from the
registry yourself. This is safe because if you're using a sqlConnection
then you're already commited to using just SQL server and so the
registry format should not change. Here's some sample code form
creating my connect string....

public DataConnection (string strUserName, string strPassword, string
strDSN)
{


Microsoft.Win32.RegistryKey key =
Microsoft.Win32.Registry.LocalMachine.OpenSubKey("SOFTWARE\\ODBC\\ODBC.I
NI\\" + strDSN);
if (key == null)
{
throw new Exception ("The specified ODBC datasource does not
exist.");
}
string databaseName = (string)key.GetValue ("Database");
string server = (string)key.GetValue ("Server");

m_strConnect = "user id=" + strUserName + ";password=" + strPassword
+ ";initial catalog=" + databaseName + ";data source=" + server;


}