sqlserver connection - need help desperately!

[peshekeedweller]

Using asp.net 1.1. vb.net 2003.

I am trying to connect to a remote sql server 2000 on a virtual machine
running windows 2000 server. I can connect through the server explorer in
visual studio, but cannot connect through code. I have done it before and
can not do it now; I don't know what changed. (I also cannot connect to
local Access database through code though I can from server explorer.)

The code is:
conn = New SqlConnection("data source=SERVERNAME;initial
catalog=DATABASE;user id=sa")

Code fails at "conn.open", returning the following error message: "SQL
Server does not exist or access denied."

SQL Server authentication is set to "sqlsever and windows" and, of course,
the "sa" account exists, with no password (though it doesn't work when I set
a password either).

Please help. I'm really getting desperate, here.

 

[Guest]

I'd suspect its not that it doesnt exist, its that you are being denied. Try
looking at this http://www.darkfalz.com/1059 to create the connection
string. So this on the VPC session, that way you know you got your access
correct before you try to do it with code.

 

[peshekeedweller]

Curt, I can create a successful connection in the data link wizard; I can
create a successful connection in the Vis Studio Server Explorer; I can
create a succesful server in the Vis Studio Server Explorer. But none of the
connection strings work in code. Even trying to use a SQLDataAdapter
component with a connection string created successfully in Server Explorer
fails when compiled and run.

What the heck is going on?

 

[Juan T. Llibre]

Try :

"Data Source=SERVERNAME;Initial Catalog=DATABASE;Persist Security Info=True;User
ID=sa;Password="

instead of

"data source=SERVERNAME;initial catalog=DATABASE;user id=sa"

Although why you would use a blank password for the sa user is beyond me.



Juan T. Llibre
ASP.NET MVP
ASP.NET FAQ : http://asp.net.do/faq/
==========================

 

[peshekeedweller]

Sorry Juan, still didin't work. Got exactly the same result.

And it is a development sql server with only 3 users and is not exposed to
the internet. That's why I don't assign password to the sa account.

 

[Guest]

All of these tests worked from WITHIN the VPC session just fine but the
connection string isnt working right? I cant think of a single instance that
would cause this. There must be a piece of info we aren't getting... a
security setting on the SQL, something...

--
Curt Christianson
site: http://www.darkfalz.com
blog: http://blog.darkfalz.com

 

[Garry Freemyer]

Well, may I be the second, one to state that this is happening to ME, over
and over. What is WORSE, is that I can take the same bloody connection
string from an app I just finished, start a new app, do the exact same thing
in the new app, and get the same results as this poor fellow is getting and
it's going to cost me my JOB if I don't solve it!!

So, guess, what, my fellow, its as common as the cold and hurts like being
burned alive!

Sign me pissed off in paradise!

 

[Damien]

Sorry Juan, still didin't work. Got exactly the same result.

And it is a development sql server with only 3 users and is not exposed to
the internet. That's why I don't assign password to the sa account.

That's funny. I'm sure Slammer didn't only hit servers that were
exposed to the internet. There's a reason why Microsoft make you jump
through more and more hoops to keep the sa password blank these days -
it's poor practice.

Also, why not set up you development server the same way your
production server is set up - you'll have less surprises when you go
live and need to work out what permissions should be set.

Damien

 

[Juan T. Llibre]

Let's summarize :

You are connecting to a remote ( not on your box ) SQL Server
which is not connected to the internet.

You can connect from within VS.NET, but fail when connecting
from ASP.NET when you use the connection strings :

"Data Source=SERVERNAME;Initial Catalog=DATABASE;Persist Security Info=True;User
ID=sa;Password=" and
"data source=SERVERNAME;initial catalog=DATABASE;user id=sa"

The SQL Server accepts both Windows and SQL Server authentication.

Given these conditions, you are getting denied access because when
ASP.NET connects to your SQL Server the credentials it passes
are those of the user account under which ASP.NET is running
on your IIS Server, and that is *not* the "sa" user, which is the
SQL Server login that's authorized to use your database.

If you can create a SQL Server login which matches your current
ASP.NET account's name *and* password, you'll surpass the
credentials problem.

In practice, though, recreating the ASP.NET password is not an easy task,
because the default setup for ASP.NET sets up an auto-generated password,
and that is a stumbling block to creating a login with the same password for
the ASP.NET account in the SQL Server.

To bypass that problem, you will have to setup a custom account to run
ASP.NET as ( so you can set the password ), enable impersonation,
and setup a login, with *exactly* the same name and password as the
ASP.NET account, in the SQL Server.

Now, in the <process model> section of your web.config, instead of using :

userName="machine" password="AutoGenerate"

you'd use : userName="YourNewASPNETAccount" password="Your7Strong9Pwd"

That way, you can repro the password in your SQL Server login.

See :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetHT01.asp
for how to do this, including the setting of the security permissions
for the directories to which the ASP.NET account must have access to.

Note: the article refers to v1.0 and that's the directory path used.
Since you are using ASP.NET 1.1, use v1.1.4322 instead.

In summary, you need to :


1. Create a New Local Account
2. Assign Minimum Privileges
3. Assign NTFS Permissions
4. Configure ASP.NET to Run Using the New Account
5. Create a login in your SQL Server with the
same name as your new ASP.NET account.
6. Assign database permissions in the SQL Server to your new login.

You should be able to connect after you do that.

 

[peshekeedweller]

I'm sure this has nothing to do with my problem, but thanks for the
condescending lecture.

 

[Juan T. Llibre]

For additional information, see :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000008.asp

 

[Damien]

I'm sure this has nothing to do with my problem, but thanks for the
condescending lecture.

I'm sorry that you took offence. Part of my attitude is probably
because I'm tasked, within our team, to maintain "best practice" with
the other developers, and so I'm more likely to advance un-asked-for
advice.

But when it comes to something as simple as Slammer, and as simple as
having blank sa passwords, I get a bit more riled up. It's a simple
rule - if all your security relies on the firewall, then sooner or
later, you're going to get burned. So I don't apologise for that.

Damien

 

[peshekeedweller]

My security doesn't rely on a firewall. My security relies on the fact that
none of my development machines are even connected (no wires, no antennas)
to the internet. Don't assume facts not in evidence.

 

[peshekeedweller]

By the way, can you help me with my problem?

I'm sorry that you took offence. Part of my attitude is probably
because I'm tasked, within our team, to maintain "best practice" with
the other developers, and so I'm more likely to advance un-asked-for
advice.

But when it comes to something as simple as Slammer, and as simple as
having blank sa passwords, I get a bit more riled up. It's a simple
rule - if all your security relies on the firewall, then sooner or
later, you're going to get burned. So I don't apologise for that.

Damien

 

[Juan T. Llibre]

I've tried to, but you prefer to argue with Damien.

Have you read my replies ?

 

[peshekeedweller]

Yes Juan, I read your reply and tried your suggestion. It didn't work. As I
said, I can connect and access data from the Server Explorer. I can create
data connections and can create servers to the sql server; both work as
expected, but cannot access through code even when I use a dataadaptor
component set to connect through a valid connection in the Server Explorer.
Very frustrating.

 

[Juan T. Llibre]

You implemented the account impersonation procedure, as outlined at :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetHT01.asp

?

And you performed all of the steps :

1. Create a New Local Account and assign a password to it
2. Assign Minimum Privileges to the account
3. Assign NTFS Permissions to the account for the appropiate directories
4. Configure ASP.NET to Run Using the New Account
5. Create a login in your SQL Server with the
same name and password as your new ASP.NET account.
6. Assign database permissions in the SQL Server to your new login.

....and it didn't work ?