Newbie question on SQL connection

[Guest]

Hello All,

I'm trying to connect to a SQL server, on my domain. During my
troubleshooting I came across this error.
""Cannot open database requested in login 'IMSmidwest'. Login fails. Login
failed for user 'LOCALHOST\ASPNET'.""
Just a quick explantion of what I got going on, basicly for troubleshooting.
My DB 'IMSmidwest' in on my Domain server, Domain name STMBL, server name
SBSERVER.
My DB on my developer computer is called 'IMS' on my computer name
LOCALHOST, on the same domain. Both DB's are exact.

My original problem is when I change my connection string to look at
SBSERVER, it tells me "USER (null) NOT A TRUSTED SQL CONNECTION", something
like that.

So for the error that I have now, IMSmidwest makes sence, because that is
the database on my domain computer, but I put down the server as Localhost.
But for user LOCALHOST\ASPNET, I do have that user, but how does that get
changed. I did make a user to have access to both SQL servers, but how can I
change a user when I'm testing my application, so I can connect to the other
SQL server.
My localhost cpu was only for developing my website, but now I want to
launch the website, but I want to use SQL on the domain. I didn't think this
would be a big deal to move my database to another server, and change my
connection strings, but I guess I was wrong.

Am I approaching this all wrong, should have I always worked with the SQL
server I intended to use for the website. If somebody can point me in the
right direction or some kind of how to move your DB to another server.

I think my problem is just a permmision thing, and not actually connecting.

Thanks as always!

Rudy

 

[Patrick Olurotimi Ige]

HI Rudy,..
Make sure SQLServer has this acct(Or just add COMPUTERNAME/ASPNET)
to the SQL Server Logins..
Hope it helps
Patrick

 

[Peter Chadwick (MCP)]

Hi,

I take it you are using integrated security for connection to your SQL
databases? In which case, the user your website uses will need access
to the SQL databases (but I think you already know that).

You can change the user your ASP.NET websites run under by changing the
"processModel" section of machine.config (usually found in somewhere
like C:\WINNT\Microsoft.NET\Framework\v1.1.4322\CONFIG), although I
would be very careful doing this on anything other than a development
machine. The ASPNET user deliberately has little permissions as it is
essentially exposed to all users accessing the site.

It sounds like you are approaching this correctly. It's good practice
to develop databases away from the live SQL server - that way in the
future you can develop changes to the database without affecting the
existing version. And the beauty of connection strings is that you can
change them to point to different databases/servers very easily.

I'm not sure why you are getting that error when connecting to your
domain SQL Server. Maybe there is a problem with permissions to your
domain SQL server? Does your connection string contain a typo?

Another option would be to set up a sql login (an actual sql server
user) and connect using the username and password properties of the
connection string rather than integrated security.

Hope this sets you off in the right direction!
Regards,

Peter Chadwick (MCP)
(e-mail address removed)

 

[Guest]

Hello Patrick and Peter,

Thank you for the advice. Patrick, I tried to add "localhost/aspnet, but
I'm not able to add non-domain user to the AD. And I don't think I really
want to.
Peter - I am using intergrated security. I think my connection string is
good, but I didn't think about adding the user to the string. Last night I
did put web online from my web server, which is also my domain server. And my
website launched, but when I went to login, it gave me an error. My login
names and password are part my database, so it's not connecting to the
database on my developer machine. Didn't try to connect to database on my
domain server, going to try that tonite.
Thanks again for the advise!

Rudy

 

[Patrick Olurotimi Ige]

Hi Rudy,
Is not that u should add it to your AD.
But the the SQL Server u are trying to connect to.
And again for connection strings go to
http://www.connectionstrings.com
And if using "intergrated security" then look for the right string on
that page.
Good Luck.
Patrick

 

[Guest]

Hello!

Wanted to give an update. I have tried your tips, still no go. But I have
made progress. With your help, and reading alot of tech notes, I am getting
a diffrent error.
I have made a new user called aspnet1, trying to by pass the machine name of
aspnet. I have set all the permission that I have been told by Microsoft.
Here is my new error.

"The 'identity' start tag on line '41' doesn't match the end tag of
'system.web' in file"


Line 97: <globalization requestEncoding="utf-8" responseEncoding="utf-8"
/>
Line 98:
Line 99: </system.web>



in my web config file. Now I did add a identy impersionate value, again a
suggestion from what I have read off the web. Here is my copy of the web
config.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>

<appSettings>
<add key="ConnectionString"
value="server=localhost;database=IMS" />

</appSettings>


<system.web>




<!-- DYNAMIC DEBUG COMPILATION
Set compilation debug="true" to insert debugging symbols (.pdb
information)
into the compiled page. Because this creates a larger file that
executes
more slowly, you should set this value to true only when debugging
and to
false at all other times. For more information, refer to the
documentation about
debugging ASP.NET files.


-->
<compilation defaultLanguage="vb" debug="true" />

<!-- CUSTOM ERROR MESSAGES
Set customErrors mode="On" or "RemoteOnly" to enable custom error
messages, "Off" to disable.
Add <error> tags for each of the errors you want to handle.
-->
<customErrors mode="RemoteOnly" />

<!-- AUTHENTICATION
This section sets the authentication policies of the application.
Possible modes are "Windows",
"Forms", "Passport" and "None"
-->
<authentication mode="Forms"></authentication>

<!--forms name="imsapp"
path="/" loginUrl="login.aspx" /-->
<identity impersonate="true" userName="STMBL\aspnet" password="tesla1515">
<forms loginUrl="login.aspx">
<credentials passwordFormat="Clear">
<user name="Teresa" password="Gracie" />

</credentials>
</forms>






<!-- AUTHORIZATION
This section sets the authorization policies of the application.
You can allow or deny access
to application resources by user or role. Wildcards: "*" mean
everyone, "?" means anonymous
(unauthenticated) users.
-->
<authorization>
<deny users="?" /> <!-- Allow all users -->



<!-- <allow users="[comma separated list of users]"
roles="[comma separated list of roles]"/>
<deny users="[comma separated list of users]"
roles="[comma separated list of roles]"/>
-->
</authorization>

<!-- APPLICATION-LEVEL TRACE LOGGING
Application-level tracing enables trace log output for every page
within an application.
Set trace enabled="true" to enable application trace logging. If
pageOutput="true", the
trace information will be displayed at the bottom of each page.
Otherwise, you can view the
application trace log by browsing the "trace.axd" page from your
web application
root.
-->
<trace enabled="false" requestLimit="10" pageOutput="false"
traceMode="SortByTime" localOnly="true" />


<!-- SESSION STATE SETTINGS
By default ASP.NET uses cookies to identify which requests belong
to a particular session.
If cookies are not available, a session can be tracked by adding a
session identifier to the URL.
To disable cookies, set sessionState cookieless="true".
-->
<sessionState
mode="InProc"
stateConnectionString="tcpip=127.0.0.1:42424"
sqlConnectionString="data source=127.0.0.1;user id=sa;password="
cookieless="false"
timeout="20"
/>

<!-- GLOBALIZATION
This section sets the globalization settings of the application.
-->
<globalization requestEncoding="utf-8" responseEncoding="utf-8" />

</system.web>

</configuration>

It's so late, I can't remember what I did exactly to get this. I try to keep
a log of steps I take, and note any changes, but I was interupted, came back
in a couple of hours, and can't remember what I did.
I do see alot of the same problems that people have with this, you would
think there would be like a basic guide how to do this. But I guess there
are alot variables, it's hard to pinpoint a sure way.
Anyway, any help would be appreciated.

Thanks!

Rudy

 

[Patrick Olurotimi Ige]

Rudy..the error u have now in the web.config is that it isn't well
formed.
Since Web.Config is XML it has to be well formed!

Try doing this :-

<identity impersonate="true" userName="STMBL\aspnet"
password="tesla1515"/>

** delete the one u have and add the one above**

If u notice i have a closed TAG at the end.
Hope it helps
Patrick

 

[Juan T. Llibre]

You have a malformed identity tag.

<identity impersonate="true" userName="STMBL\aspnet" password="tesla1515">

Use :
<identity impersonate="true" userName="STMBL\aspnet" password="tesla1515" />

Btw, create another user account
and password to impersonate.

If someone links your name to an IP, they'll be able to
login remotely to your computer using that credential
you published here

*NEVER* publish real computer usernames/passwords to the Web.




Juan T. Llibre
ASP.NET MVP
===========

 

[Guest]

Hello all!

Well, I hate to beat a dead horse, but I'm going to ride this dead thing
till I get it. I really appreciate everyones input. If anybody wanted to
make a trip to Wisconsin, there would be a case of your favorite brew. LOL
OK, So I fixed my web config, took out the Identity Impersonate, and fixed
some simple syntax errors. Now when I use this code
''' Protected Const SQL_CONNECTION_STRING As String = _
''' "Persist Security Info=False; Server=sbserver;" & _
''' "Trusted_Connection=Yes; DataBase=IMSmidwest;" & _
''' "Integrated Security=SSPI"
I get Login failed for user 'STMBL\ASPNET'. Reason: Not associated with a
trusted SQL Server connection.

With this code
Protected Const SQL_CONNECTION_STRING As String = _
"Data Source=192.168.100.120,1533;Network Library=dbmssocn;Initial
Catalog=IMSmidwest;User ID=ASPNET;Password=******;"

I get Login failed for user 'STMBL\ASPNET'.

One thing I have noticed is when I run SQL query Analizer, I can't login
with SQL sever authentication, but I can with windows. Now I have my server
setup windows Only, I tried Windows and SQL server. It didn't make a
diffrence. I'm so deep into this, I'm not even sure what I'm asking anymore.
I noticed that ASPNET was made for me as a user on my develper computer, but
on my domain I had to make the user myself. But it's still a diffrent user,
"localhost\aspnet vs STMBL\aspnet.
I can still can connect to my server in SQL manager, and I can work with
both SQL servers. I have a feeling that I'm trying so many diffrent things,
that the combination of settings is wrong, just not sure which is the best
way to approach this. Any other thoughts on this?

Thanks for your time!

Rudy

 

[IPGrunt]

Hello all!

Well, I hate to beat a dead horse, but I'm going to ride this dead thing
till I get it. I really appreciate everyones input. If anybody wanted to
make a trip to Wisconsin, there would be a case of your favorite brew. LOL
OK, So I fixed my web config, took out the Identity Impersonate, and fixed
some simple syntax errors. Now when I use this code
''' Protected Const SQL_CONNECTION_STRING As String = _
''' "Persist Security Info=False; Server=sbserver;" & _
''' "Trusted_Connection=Yes; DataBase=IMSmidwest;" & _
''' "Integrated Security=SSPI"
I get Login failed for user 'STMBL\ASPNET'. Reason: Not associated with a
trusted SQL Server connection.

With this code
Protected Const SQL_CONNECTION_STRING As String = _
"Data Source=192.168.100.120,1533;Network Library=dbmssocn;Initial
Catalog=IMSmidwest;User ID=ASPNET;Password=******;"

I get Login failed for user 'STMBL\ASPNET'.

One thing I have noticed is when I run SQL query Analizer, I can't login
with SQL sever authentication, but I can with windows. Now I have my server
setup windows Only, I tried Windows and SQL server. It didn't make a
diffrence. I'm so deep into this, I'm not even sure what I'm asking anymore.
I noticed that ASPNET was made for me as a user on my develper computer, but
on my domain I had to make the user myself. But it's still a diffrent user,
"localhost\aspnet vs STMBL\aspnet.
I can still can connect to my server in SQL manager, and I can work with
both SQL servers. I have a feeling that I'm trying so many diffrent things,
that the combination of settings is wrong, just not sure which is the best
way to approach this. Any other thoughts on this?

Thanks for your time!

Rudy

I think I've had this problem in the past, way in the past. It is probably
due to the fact that you are using sending the wrong credentials (domain
vs. local).

There is a trick whereby you use the username only in connection string,
and set the same password for both the domain and local account user
(ASPNET). There is a KB article on this. This way, no matter what
credential the server is getting will work.

But, slow down and make your changes one at a time. Keep a notebook. And
use the KB. You'll never get anywhere if you don't. This is the kind of
problem that can cost you a week.

-- ipgrunt

 

[Patrick Olurotimi Ige]

Rudy,
Take IPGrunt advice do it in steps and if possible take notes!!What i
can advice is that since u are using Windows Auth integrated now and ur
getting denied access with user STMBL\ASPNET'.

Try using the string "server=(local);database=pubs;integrated
security=true;"

where server= SQLSERVERNAME and database= URDATABASENAME

Is STMBL ur computer name?

Bcos the ASPNET should be COMPUTERNAME/ASPNET.

Hope it helps..
Patrick

 

[Kevin Spencer]

Hi Rudy,

Let me educate you about SQL Server security. SQL Server has 2 security
configurations, as you've discovered. One is "Windows Only" and the other is
"Mixed." The "Trusted Connection" error is associated with the "Windows
Only" security mode. In the "Windows Only" security mode, only valid domain
accounts that are specifically granted certain types of permissions in the
SQL
Server have access. Note that there are 2 conditions: First, a Windows
domain user account. Second, granting permissions to these accounts in SQL
Server. The second security model, "Mixed," means that SQL Server can
maintain its own user accounts, which are separate from domain user
accounts. These are SQL Server only. Note that "Mixed" means that you can
have both Windows domain user accounts and SQL Server user accounts in SQL
Server. In addition to creating these accounts, whether domain or SQL Server
accounts, you must again grant SQL Server permissions to the accounts.

In other words, regardless of whether you use "Windows Only" or "Mixed," you
need to specifically grant the accounts the permissions they need to access
SQL Server objects. Simply logging in with a domain user account doesn't
allow the account to access anything, any more than creating a domain user
account doesn't automatically grant any domain privileges to that account.

In conclusion:

"Windows Only": Log in with domain account that has been granted explicit
database permissions.
"Mixed": Log in with either domain account or SQL Server account that has
been granted explicit database permissions.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

 

[Guest]

Hi Pat and IP,

I will try the same password thing tonite. I did try the string below I
think at one point, but I will try it again. STMBL is the name of my domain
Patrick. The computer name of my domain server server is SBSERVER. Maybe I
should try SBSERVER\ASPNET instead of STMBL. But I thought in a domain
enviroment, I need to put the domain name as part of the name.
I'm actually installing a SAN at a clients today. They not only do
television post production, (the bussiness that I'm in), but they do web
development as well. I'm going to try to see if I can talk to one of the
developers over there.
Thank you all for you advice and time.
Talk to you soon!!

Rudy

 

[Guest]

THANK YOU THANK YOU THANK YOU!!

Kevin, you removed the block, I finally got it!! I knew it was something
simple, just doing it the wrong way. Thanks to all you helped me out with
this, I really appreciate all of your time and knowledge.

Rudy