DB access works in debug but fails when hosted

I have written a intranet page that writes some info into a sql database, basically following the 'SQL Server 2005 Express for Beginners' video.

When I debug the application from within 'Visual Web Develop 2005 express' it works fine entries are entered into the DB and I can then edit the db using the admin page.

But when I host the site using IIS I doesn't work, submissions to the database seem to fail I can see the DB in the admin page but if I try to edit them or delete them it fails.

What could I doing wrong could I be missing a setting in IIS? Any ideas??

Here's my webconfig if that helps at all:

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<connectionStrings>
<add name="studentprofilesConnectionString1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\studentprofiles.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<roleManager defaultProvider="AspNetWindowsTokenRoleProvider" />
<compilation debug="true" defaultLanguage="c#" /></system.web></configuration

There is a posibility that if SQL server is not configured to accept remote connection it may fail. To enable remote connection follow this steps

Under SQL2005 program menu follow configuration Tools =>SQL Server Surface Area Configuration => Select Server Surface Area Configuration for Services and Connections.

It will lead to a window with a treeview select Remote connections.Then Select Local and Remote Connections.

Now if the error is due to Remote configuration issue it will be resolved

DB Access to more than one DB on the Same Server

I am trying to grant access to more than one database on the same SQL serve
r.
I have created two databases (Helpdesk, Helpdesk_2) on SQL Server Server1.
I have given User1 acces to both databases. I have made the DB "HELPDESK"
User1s default DB. When User1 tries to create an ODBC connection to DB "HELP
DESK_2"
he is not able to se the database(HELPDESK_2) in the list.
In Sybase when this happens, I just change the default database to "MASTER"
and this
clears up the problem. I have tried this in SQL Server and the problem still
persists.
What is it that I am overlooking.
Configuration: SQL Server Enterprise SP3 Processor License.
MS Windows 2K SP3If he run the statement "select * from master..sysdatabases" does it return
the full list?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Yes, all the databases appear when they run a select on sysdatabases.
-- VFLORES wrote: --
I am trying to grant access to more than one database on the same SQL serve
r.
I have created two databases (Helpdesk, Helpdesk_2) on SQL Server Server1.
I have given User1 acces to both databases. I have made the DB "HELPDESK"
User1s default DB. When User1 tries to create an ODBC connection to DB "HELP
DESK_2"
he is not able to se the database(HELPDESK_2) in the list.
In Sybase when this happens, I just change the default database to "MASTER"
and this
clears up the problem. I have tried this in SQL Server and the problem still
persists.
What is it that I am overlooking.
Configuration: SQL Server Enterprise SP3 Processor License.
MS Windows 2K SP3|||I would try running SQL profiler to trace the problem.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sql

DB Access to another server w/NT AUTHORITY

We have a SQL Server 2000 database named acmedb on a Server2003 machine
named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
asp.net 2.0 website on that same server we use the following connection
string...
Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
This connection works fine. But now we want to run the website on SERVERTWO
and access the database on SERVERONE. Using that same connection string we
can't connect. we get: Login failed for user (null). Reason: Not
associated with a trusted SQL Server connection. The server database is
setup for both Windows and SQL server authentication so that is not the
cause.
The two servers are in two different workgroups but SERVERTWO can ping
SERVERONE.
I really don't want to change ownership of the db and have to pass passwords
and UIDs in the connection string. Is there a way to make this work this
way?
Thanks,
T
You get that error because the service account that you use to login to your
SQL Server is not existed on both servers I believe.
You'd be able to connect if you'd be using a SQL Server Login, not NT
AUTHORITY as Windows Authentication. Then you'd need to change your
connection string to use uid and pwd instead of integrated security.
You could also use a method in which you'd create two identical service
accounts on both servers and use this account to login to SQL Server. Then
you would be able to use integrated security in your connection string
again.
Ekrem nsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
> We have a SQL Server 2000 database named acmedb on a Server2003 machine
> named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
> asp.net 2.0 website on that same server we use the following connection
> string...
> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
> This connection works fine. But now we want to run the website on
> SERVERTWO and access the database on SERVERONE. Using that same
> connection string we can't connect. we get: Login failed for user (null).
> Reason: Not associated with a trusted SQL Server connection. The server
> database is setup for both Windows and SQL server authentication so that
> is not the cause.
> The two servers are in two different workgroups but SERVERTWO can ping
> SERVERONE.
> I really don't want to change ownership of the db and have to pass
> passwords and UIDs in the connection string. Is there a way to make this
> work this way?
> Thanks,
> T
>
|||Your problem has little to do with the ownership of the database on
SERVERONE. It is the APS.NET app. That is, the user account used to run the
ASP.NET application does not have access to the database on SERVERONE.
You need to configure the ASP.NET app correctly to access SQL Server.
The simple way would be to use SQL Server security, instead of Windows
security to log into SQL Server, as other other post suggested. So, you add
username/password to the ConnectionString.
However, if you need to use Windows authentication to access SQL Server, you
have to configure ASP.NET APP correctly. The ASP.NET APP cannot use local
account from SERVERTWO to access SQL Server on SERVERONE. Period (unless it
is peer-to-peer network).
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
> We have a SQL Server 2000 database named acmedb on a Server2003 machine
> named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
> asp.net 2.0 website on that same server we use the following connection
> string...
> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
> This connection works fine. But now we want to run the website on
> SERVERTWO and access the database on SERVERONE. Using that same
> connection string we can't connect. we get: Login failed for user (null).
> Reason: Not associated with a trusted SQL Server connection. The server
> database is setup for both Windows and SQL server authentication so that
> is not the cause.
> The two servers are in two different workgroups but SERVERTWO can ping
> SERVERONE.
> I really don't want to change ownership of the db and have to pass
> passwords and UIDs in the connection string. Is there a way to make this
> work this way?
> Thanks,
> T
>
|||Ekrem,
when you say...
You could also use a method in which you'd create two identical service
accounts on both servers and use this account to login to SQL Server. Then
you would be able to use integrated security in your connection string
again.
Can you give me an example of "Identical Service Accounts"?
Thanks,
T
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:D7784117-876B-4209-9EC8-9087194541E9@.microsoft.com...
> You get that error because the service account that you use to login to
> your SQL Server is not existed on both servers I believe.
> You'd be able to connect if you'd be using a SQL Server Login, not NT
> AUTHORITY as Windows Authentication. Then you'd need to change your
> connection string to use uid and pwd instead of integrated security.
> You could also use a method in which you'd create two identical service
> accounts on both servers and use this account to login to SQL Server. Then
> you would be able to use integrated security in your connection string
> again.
> --
> Ekrem nsoy
>
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
>
|||For example, create an account like the following on your first server
username = xxx
pass = ooo
create another account on your second server
username = xxx
pass = ooo
Usernames and passwords are the same \ identical. And create a login in your
SQL Server for this account. Then you'll be able to login to your SQL Server
using this account and so using Windows Authentication.
Logon to your SERVERONE Windows using this xxx account and you'll be able to
login to your SQL Server under this account.
Ekrem nsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uchd5o0NIHA.5524@.TK2MSFTNGP05.phx.gbl...
> Ekrem,
> when you say...
> You could also use a method in which you'd create two identical service
> accounts on both servers and use this account to login to SQL Server. Then
> you would be able to use integrated security in your connection string
> again.
> Can you give me an example of "Identical Service Accounts"?
> Thanks,
> T
>
>
> "Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
> news:D7784117-876B-4209-9EC8-9087194541E9@.microsoft.com...
>
|||so you are saying that the local account on the SERVERONE database server is
setup for Windows Authentication to the DB. right?
Then the connectionstring on the asp.net app running on SERVERTWO would not
have to include PWDand UID? What would that connectionstring look like?
Would it just have UID but no PWD?
T
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:62A50A48-512B-4F76-9351-1E16678A0108@.microsoft.com...
> For example, create an account like the following on your first server
> username = xxx
> pass = ooo
> create another account on your second server
> username = xxx
> pass = ooo
> Usernames and passwords are the same \ identical. And create a login in
> your SQL Server for this account. Then you'll be able to login to your SQL
> Server using this account and so using Windows Authentication.
> Logon to your SERVERONE Windows using this xxx account and you'll be able
> to login to your SQL Server under this account.
> --
> Ekrem nsoy
>
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uchd5o0NIHA.5524@.TK2MSFTNGP05.phx.gbl...
>
|||So, are you saying that I can't do what I want to do? Whatever I do I will
need PWD and UID in the connectionstring on SERVERTWO?
T
"Norman Yuan" <NoAddress@.NoEmail.fake> wrote in message
news:eDQgkm0NIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Your problem has little to do with the ownership of the database on
> SERVERONE. It is the APS.NET app. That is, the user account used to run
> the ASP.NET application does not have access to the database on SERVERONE.
> You need to configure the ASP.NET app correctly to access SQL Server.
> The simple way would be to use SQL Server security, instead of Windows
> security to log into SQL Server, as other other post suggested. So, you
> add username/password to the ConnectionString.
> However, if you need to use Windows authentication to access SQL Server,
> you have to configure ASP.NET APP correctly. The ASP.NET APP cannot use
> local account from SERVERTWO to access SQL Server on SERVERONE. Period
> (unless it is peer-to-peer network).
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
>
|||Without knowing details on your system setup (domain network, peer-to-peer),
I cannot say more. Basically, you need some consideration on your ASP.NET
app side: which user account you want to use to run the ASP.NET app,
considersing the application's need to access resources locally or in the
network, and then from security point of view. Then you can tell if the
choosen account has necessary access to the SQL Server database in the other
computer.
So, I am not saying anything other than your ConnectionString shown in your
original post does not work. Again, cannt say more more before knowing more
details on your ASP.NET app configuration, network...
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:eV%230Gq1NIHA.6108@.TK2MSFTNGP03.phx.gbl...
> So, are you saying that I can't do what I want to do? Whatever I do I
> will need PWD and UID in the connectionstring on SERVERTWO?
> T
>
> "Norman Yuan" <NoAddress@.NoEmail.fake> wrote in message
> news:eDQgkm0NIHA.1184@.TK2MSFTNGP04.phx.gbl...
>

DB Access to another server w/NT AUTHORITY

We have a SQL Server 2000 database named acmedb on a Server2003 machine
named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
asp.net 2.0 website on that same server we use the following connection
string...
Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
This connection works fine. But now we want to run the website on SERVERTWO
and access the database on SERVERONE. Using that same connection string we
can't connect. we get: Login failed for user (null). Reason: Not
associated with a trusted SQL Server connection. The server database is
setup for both Windows and SQL server authentication so that is not the
cause.
The two servers are in two different workgroups but SERVERTWO can ping
SERVERONE.
I really don't want to change ownership of the db and have to pass passwords
and UIDs in the connection string. Is there a way to make this work this
way?
Thanks,
TYou get that error because the service account that you use to login to your
SQL Server is not existed on both servers I believe.
You'd be able to connect if you'd be using a SQL Server Login, not NT
AUTHORITY as Windows Authentication. Then you'd need to change your
connection string to use uid and pwd instead of integrated security.
You could also use a method in which you'd create two identical service
accounts on both servers and use this account to login to SQL Server. Then
you would be able to use integrated security in your connection string
again.
--
Ekrem Önsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
> We have a SQL Server 2000 database named acmedb on a Server2003 machine
> named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
> asp.net 2.0 website on that same server we use the following connection
> string...
> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
> This connection works fine. But now we want to run the website on
> SERVERTWO and access the database on SERVERONE. Using that same
> connection string we can't connect. we get: Login failed for user (null).
> Reason: Not associated with a trusted SQL Server connection. The server
> database is setup for both Windows and SQL server authentication so that
> is not the cause.
> The two servers are in two different workgroups but SERVERTWO can ping
> SERVERONE.
> I really don't want to change ownership of the db and have to pass
> passwords and UIDs in the connection string. Is there a way to make this
> work this way?
> Thanks,
> T
>|||Your problem has little to do with the ownership of the database on
SERVERONE. It is the APS.NET app. That is, the user account used to run the
ASP.NET application does not have access to the database on SERVERONE.
You need to configure the ASP.NET app correctly to access SQL Server.
The simple way would be to use SQL Server security, instead of Windows
security to log into SQL Server, as other other post suggested. So, you add
username/password to the ConnectionString.
However, if you need to use Windows authentication to access SQL Server, you
have to configure ASP.NET APP correctly. The ASP.NET APP cannot use local
account from SERVERTWO to access SQL Server on SERVERONE. Period (unless it
is peer-to-peer network).
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
> We have a SQL Server 2000 database named acmedb on a Server2003 machine
> named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
> asp.net 2.0 website on that same server we use the following connection
> string...
> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
> This connection works fine. But now we want to run the website on
> SERVERTWO and access the database on SERVERONE. Using that same
> connection string we can't connect. we get: Login failed for user (null).
> Reason: Not associated with a trusted SQL Server connection. The server
> database is setup for both Windows and SQL server authentication so that
> is not the cause.
> The two servers are in two different workgroups but SERVERTWO can ping
> SERVERONE.
> I really don't want to change ownership of the db and have to pass
> passwords and UIDs in the connection string. Is there a way to make this
> work this way?
> Thanks,
> T
>|||Ekrem,
when you say...
You could also use a method in which you'd create two identical service
accounts on both servers and use this account to login to SQL Server. Then
you would be able to use integrated security in your connection string
again.
Can you give me an example of "Identical Service Accounts"?
Thanks,
T
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:D7784117-876B-4209-9EC8-9087194541E9@.microsoft.com...
> You get that error because the service account that you use to login to
> your SQL Server is not existed on both servers I believe.
> You'd be able to connect if you'd be using a SQL Server Login, not NT
> AUTHORITY as Windows Authentication. Then you'd need to change your
> connection string to use uid and pwd instead of integrated security.
> You could also use a method in which you'd create two identical service
> accounts on both servers and use this account to login to SQL Server. Then
> you would be able to use integrated security in your connection string
> again.
> --
> Ekrem Önsoy
>
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> We have a SQL Server 2000 database named acmedb on a Server2003 machine
>> named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
>> asp.net 2.0 website on that same server we use the following connection
>> string...
>> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
>> This connection works fine. But now we want to run the website on
>> SERVERTWO and access the database on SERVERONE. Using that same
>> connection string we can't connect. we get: Login failed for user
>> (null). Reason: Not associated with a trusted SQL Server connection. The
>> server database is setup for both Windows and SQL server authentication
>> so that is not the cause.
>> The two servers are in two different workgroups but SERVERTWO can ping
>> SERVERONE.
>> I really don't want to change ownership of the db and have to pass
>> passwords and UIDs in the connection string. Is there a way to make this
>> work this way?
>> Thanks,
>> T
>|||For example, create an account like the following on your first server
username = xxx
pass = ooo
create another account on your second server
username = xxx
pass = ooo
Usernames and passwords are the same \ identical. And create a login in your
SQL Server for this account. Then you'll be able to login to your SQL Server
using this account and so using Windows Authentication.
Logon to your SERVERONE Windows using this xxx account and you'll be able to
login to your SQL Server under this account.
--
Ekrem Önsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uchd5o0NIHA.5524@.TK2MSFTNGP05.phx.gbl...
> Ekrem,
> when you say...
> You could also use a method in which you'd create two identical service
> accounts on both servers and use this account to login to SQL Server. Then
> you would be able to use integrated security in your connection string
> again.
> Can you give me an example of "Identical Service Accounts"?
> Thanks,
> T
>
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:D7784117-876B-4209-9EC8-9087194541E9@.microsoft.com...
>> You get that error because the service account that you use to login to
>> your SQL Server is not existed on both servers I believe.
>> You'd be able to connect if you'd be using a SQL Server Login, not NT
>> AUTHORITY as Windows Authentication. Then you'd need to change your
>> connection string to use uid and pwd instead of integrated security.
>> You could also use a method in which you'd create two identical service
>> accounts on both servers and use this account to login to SQL Server.
>> Then you would be able to use integrated security in your connection
>> string again.
>> --
>> Ekrem Önsoy
>>
>> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
>> news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> We have a SQL Server 2000 database named acmedb on a Server2003 machine
>> named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
>> asp.net 2.0 website on that same server we use the following connection
>> string...
>> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
>> This connection works fine. But now we want to run the website on
>> SERVERTWO and access the database on SERVERONE. Using that same
>> connection string we can't connect. we get: Login failed for user
>> (null). Reason: Not associated with a trusted SQL Server connection.
>> The server database is setup for both Windows and SQL server
>> authentication so that is not the cause.
>> The two servers are in two different workgroups but SERVERTWO can ping
>> SERVERONE.
>> I really don't want to change ownership of the db and have to pass
>> passwords and UIDs in the connection string. Is there a way to make
>> this work this way?
>> Thanks,
>> T
>>
>|||so you are saying that the local account on the SERVERONE database server is
setup for Windows Authentication to the DB. right?
Then the connectionstring on the asp.net app running on SERVERTWO would not
have to include PWDand UID? What would that connectionstring look like?
Would it just have UID but no PWD?
T
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:62A50A48-512B-4F76-9351-1E16678A0108@.microsoft.com...
> For example, create an account like the following on your first server
> username = xxx
> pass = ooo
> create another account on your second server
> username = xxx
> pass = ooo
> Usernames and passwords are the same \ identical. And create a login in
> your SQL Server for this account. Then you'll be able to login to your SQL
> Server using this account and so using Windows Authentication.
> Logon to your SERVERONE Windows using this xxx account and you'll be able
> to login to your SQL Server under this account.
> --
> Ekrem Önsoy
>
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uchd5o0NIHA.5524@.TK2MSFTNGP05.phx.gbl...
>> Ekrem,
>> when you say...
>> You could also use a method in which you'd create two identical service
>> accounts on both servers and use this account to login to SQL Server.
>> Then
>> you would be able to use integrated security in your connection string
>> again.
>> Can you give me an example of "Identical Service Accounts"?
>> Thanks,
>> T
>>
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:D7784117-876B-4209-9EC8-9087194541E9@.microsoft.com...
>> You get that error because the service account that you use to login to
>> your SQL Server is not existed on both servers I believe.
>> You'd be able to connect if you'd be using a SQL Server Login, not NT
>> AUTHORITY as Windows Authentication. Then you'd need to change your
>> connection string to use uid and pwd instead of integrated security.
>> You could also use a method in which you'd create two identical service
>> accounts on both servers and use this account to login to SQL Server.
>> Then you would be able to use integrated security in your connection
>> string again.
>> --
>> Ekrem Önsoy
>>
>> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
>> news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> We have a SQL Server 2000 database named acmedb on a Server2003 machine
>> named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
>> asp.net 2.0 website on that same server we use the following connection
>> string...
>> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
>> This connection works fine. But now we want to run the website on
>> SERVERTWO and access the database on SERVERONE. Using that same
>> connection string we can't connect. we get: Login failed for user
>> (null). Reason: Not associated with a trusted SQL Server connection.
>> The server database is setup for both Windows and SQL server
>> authentication so that is not the cause.
>> The two servers are in two different workgroups but SERVERTWO can ping
>> SERVERONE.
>> I really don't want to change ownership of the db and have to pass
>> passwords and UIDs in the connection string. Is there a way to make
>> this work this way?
>> Thanks,
>> T
>>
>>
>|||So, are you saying that I can't do what I want to do? Whatever I do I will
need PWD and UID in the connectionstring on SERVERTWO?
T
"Norman Yuan" <NoAddress@.NoEmail.fake> wrote in message
news:eDQgkm0NIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Your problem has little to do with the ownership of the database on
> SERVERONE. It is the APS.NET app. That is, the user account used to run
> the ASP.NET application does not have access to the database on SERVERONE.
> You need to configure the ASP.NET app correctly to access SQL Server.
> The simple way would be to use SQL Server security, instead of Windows
> security to log into SQL Server, as other other post suggested. So, you
> add username/password to the ConnectionString.
> However, if you need to use Windows authentication to access SQL Server,
> you have to configure ASP.NET APP correctly. The ASP.NET APP cannot use
> local account from SERVERTWO to access SQL Server on SERVERONE. Period
> (unless it is peer-to-peer network).
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> We have a SQL Server 2000 database named acmedb on a Server2003 machine
>> named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
>> asp.net 2.0 website on that same server we use the following connection
>> string...
>> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
>> This connection works fine. But now we want to run the website on
>> SERVERTWO and access the database on SERVERONE. Using that same
>> connection string we can't connect. we get: Login failed for user
>> (null). Reason: Not associated with a trusted SQL Server connection. The
>> server database is setup for both Windows and SQL server authentication
>> so that is not the cause.
>> The two servers are in two different workgroups but SERVERTWO can ping
>> SERVERONE.
>> I really don't want to change ownership of the db and have to pass
>> passwords and UIDs in the connection string. Is there a way to make this
>> work this way?
>> Thanks,
>> T
>|||You'll use the same conn str. Just create the Windows user accounts and
create a login in sql server for this account and use this windows account
for your web application's windows session.
--
Ekrem Önsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:ODhkLp1NIHA.5400@.TK2MSFTNGP04.phx.gbl...
> so you are saying that the local account on the SERVERONE database server
> is setup for Windows Authentication to the DB. right?
> Then the connectionstring on the asp.net app running on SERVERTWO would
> not have to include PWDand UID? What would that connectionstring look
> like? Would it just have UID but no PWD?
> T
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:62A50A48-512B-4F76-9351-1E16678A0108@.microsoft.com...
>> For example, create an account like the following on your first server
>> username = xxx
>> pass = ooo
>> create another account on your second server
>> username = xxx
>> pass = ooo
>> Usernames and passwords are the same \ identical. And create a login in
>> your SQL Server for this account. Then you'll be able to login to your
>> SQL Server using this account and so using Windows Authentication.
>> Logon to your SERVERONE Windows using this xxx account and you'll be able
>> to login to your SQL Server under this account.
>> --
>> Ekrem Önsoy
>>
>> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
>> news:uchd5o0NIHA.5524@.TK2MSFTNGP05.phx.gbl...
>> Ekrem,
>> when you say...
>> You could also use a method in which you'd create two identical service
>> accounts on both servers and use this account to login to SQL Server.
>> Then
>> you would be able to use integrated security in your connection string
>> again.
>> Can you give me an example of "Identical Service Accounts"?
>> Thanks,
>> T
>>
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:D7784117-876B-4209-9EC8-9087194541E9@.microsoft.com...
>> You get that error because the service account that you use to login to
>> your SQL Server is not existed on both servers I believe.
>> You'd be able to connect if you'd be using a SQL Server Login, not NT
>> AUTHORITY as Windows Authentication. Then you'd need to change your
>> connection string to use uid and pwd instead of integrated security.
>> You could also use a method in which you'd create two identical service
>> accounts on both servers and use this account to login to SQL Server.
>> Then you would be able to use integrated security in your connection
>> string again.
>> --
>> Ekrem Önsoy
>>
>> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
>> news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> We have a SQL Server 2000 database named acmedb on a Server2003
>> machine named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When
>> we run our asp.net 2.0 website on that same server we use the
>> following connection string...
>> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
>> This connection works fine. But now we want to run the website on
>> SERVERTWO and access the database on SERVERONE. Using that same
>> connection string we can't connect. we get: Login failed for user
>> (null). Reason: Not associated with a trusted SQL Server connection.
>> The server database is setup for both Windows and SQL server
>> authentication so that is not the cause.
>> The two servers are in two different workgroups but SERVERTWO can ping
>> SERVERONE.
>> I really don't want to change ownership of the db and have to pass
>> passwords and UIDs in the connection string. Is there a way to make
>> this work this way?
>> Thanks,
>> T
>>
>>
>|||Without knowing details on your system setup (domain network, peer-to-peer),
I cannot say more. Basically, you need some consideration on your ASP.NET
app side: which user account you want to use to run the ASP.NET app,
considersing the application's need to access resources locally or in the
network, and then from security point of view. Then you can tell if the
choosen account has necessary access to the SQL Server database in the other
computer.
So, I am not saying anything other than your ConnectionString shown in your
original post does not work. Again, cannt say more more before knowing more
details on your ASP.NET app configuration, network...
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:eV%230Gq1NIHA.6108@.TK2MSFTNGP03.phx.gbl...
> So, are you saying that I can't do what I want to do? Whatever I do I
> will need PWD and UID in the connectionstring on SERVERTWO?
> T
>
> "Norman Yuan" <NoAddress@.NoEmail.fake> wrote in message
> news:eDQgkm0NIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Your problem has little to do with the ownership of the database on
>> SERVERONE. It is the APS.NET app. That is, the user account used to run
>> the ASP.NET application does not have access to the database on
>> SERVERONE.
>> You need to configure the ASP.NET app correctly to access SQL Server.
>> The simple way would be to use SQL Server security, instead of Windows
>> security to log into SQL Server, as other other post suggested. So, you
>> add username/password to the ConnectionString.
>> However, if you need to use Windows authentication to access SQL Server,
>> you have to configure ASP.NET APP correctly. The ASP.NET APP cannot use
>> local account from SERVERTWO to access SQL Server on SERVERONE. Period
>> (unless it is peer-to-peer network).
>> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
>> news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> We have a SQL Server 2000 database named acmedb on a Server2003 machine
>> named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
>> asp.net 2.0 website on that same server we use the following connection
>> string...
>> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
>> This connection works fine. But now we want to run the website on
>> SERVERTWO and access the database on SERVERONE. Using that same
>> connection string we can't connect. we get: Login failed for user
>> (null). Reason: Not associated with a trusted SQL Server connection.
>> The server database is setup for both Windows and SQL server
>> authentication so that is not the cause.
>> The two servers are in two different workgroups but SERVERTWO can ping
>> SERVERONE.
>> I really don't want to change ownership of the db and have to pass
>> passwords and UIDs in the connection string. Is there a way to make
>> this work this way?
>> Thanks,
>> T
>>
>|||I think your solution would only work if the two servers are in the same
domain - right?
T
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:E0E94E38-299C-4100-9DE8-16786A19000A@.microsoft.com...
> You'll use the same conn str. Just create the Windows user accounts and
> create a login in sql server for this account and use this windows account
> for your web application's windows session.
> --
> Ekrem Önsoy
>
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:ODhkLp1NIHA.5400@.TK2MSFTNGP04.phx.gbl...
>> so you are saying that the local account on the SERVERONE database server
>> is setup for Windows Authentication to the DB. right?
>> Then the connectionstring on the asp.net app running on SERVERTWO would
>> not have to include PWDand UID? What would that connectionstring look
>> like? Would it just have UID but no PWD?
>> T
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:62A50A48-512B-4F76-9351-1E16678A0108@.microsoft.com...
>> For example, create an account like the following on your first server
>> username = xxx
>> pass = ooo
>> create another account on your second server
>> username = xxx
>> pass = ooo
>> Usernames and passwords are the same \ identical. And create a login in
>> your SQL Server for this account. Then you'll be able to login to your
>> SQL Server using this account and so using Windows Authentication.
>> Logon to your SERVERONE Windows using this xxx account and you'll be
>> able to login to your SQL Server under this account.
>> --
>> Ekrem Önsoy
>>
>> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
>> news:uchd5o0NIHA.5524@.TK2MSFTNGP05.phx.gbl...
>> Ekrem,
>> when you say...
>> You could also use a method in which you'd create two identical service
>> accounts on both servers and use this account to login to SQL Server.
>> Then
>> you would be able to use integrated security in your connection string
>> again.
>> Can you give me an example of "Identical Service Accounts"?
>> Thanks,
>> T
>>
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:D7784117-876B-4209-9EC8-9087194541E9@.microsoft.com...
>> You get that error because the service account that you use to login
>> to your SQL Server is not existed on both servers I believe.
>> You'd be able to connect if you'd be using a SQL Server Login, not NT
>> AUTHORITY as Windows Authentication. Then you'd need to change your
>> connection string to use uid and pwd instead of integrated security.
>> You could also use a method in which you'd create two identical
>> service accounts on both servers and use this account to login to SQL
>> Server. Then you would be able to use integrated security in your
>> connection string again.
>> --
>> Ekrem Önsoy
>>
>> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
>> news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> We have a SQL Server 2000 database named acmedb on a Server2003
>> machine named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When
>> we run our asp.net 2.0 website on that same server we use the
>> following connection string...
>> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
>> This connection works fine. But now we want to run the website on
>> SERVERTWO and access the database on SERVERONE. Using that same
>> connection string we can't connect. we get: Login failed for user
>> (null). Reason: Not associated with a trusted SQL Server connection.
>> The server database is setup for both Windows and SQL server
>> authentication so that is not the cause.
>> The two servers are in two different workgroups but SERVERTWO can
>> ping SERVERONE.
>> I really don't want to change ownership of the db and have to pass
>> passwords and UIDs in the connection string. Is there a way to make
>> this work this way?
>> Thanks,
>> T
>>
>>
>>
>|||Your servers don't have to be in a domain but in the same network to make
this work.
--
Ekrem Önsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:ORLwLI6NIHA.2208@.TK2MSFTNGP06.phx.gbl...
>I think your solution would only work if the two servers are in the same
>domain - right?
> T
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:E0E94E38-299C-4100-9DE8-16786A19000A@.microsoft.com...
>> You'll use the same conn str. Just create the Windows user accounts and
>> create a login in sql server for this account and use this windows
>> account for your web application's windows session.
>> --
>> Ekrem Önsoy
>>
>> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
>> news:ODhkLp1NIHA.5400@.TK2MSFTNGP04.phx.gbl...
>> so you are saying that the local account on the SERVERONE database
>> server is setup for Windows Authentication to the DB. right?
>> Then the connectionstring on the asp.net app running on SERVERTWO would
>> not have to include PWDand UID? What would that connectionstring look
>> like? Would it just have UID but no PWD?
>> T
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:62A50A48-512B-4F76-9351-1E16678A0108@.microsoft.com...
>> For example, create an account like the following on your first server
>> username = xxx
>> pass = ooo
>> create another account on your second server
>> username = xxx
>> pass = ooo
>> Usernames and passwords are the same \ identical. And create a login in
>> your SQL Server for this account. Then you'll be able to login to your
>> SQL Server using this account and so using Windows Authentication.
>> Logon to your SERVERONE Windows using this xxx account and you'll be
>> able to login to your SQL Server under this account.
>> --
>> Ekrem Önsoy
>>
>> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
>> news:uchd5o0NIHA.5524@.TK2MSFTNGP05.phx.gbl...
>> Ekrem,
>> when you say...
>> You could also use a method in which you'd create two identical
>> service
>> accounts on both servers and use this account to login to SQL Server.
>> Then
>> you would be able to use integrated security in your connection string
>> again.
>> Can you give me an example of "Identical Service Accounts"?
>> Thanks,
>> T
>>
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:D7784117-876B-4209-9EC8-9087194541E9@.microsoft.com...
>> You get that error because the service account that you use to login
>> to your SQL Server is not existed on both servers I believe.
>> You'd be able to connect if you'd be using a SQL Server Login, not NT
>> AUTHORITY as Windows Authentication. Then you'd need to change your
>> connection string to use uid and pwd instead of integrated security.
>> You could also use a method in which you'd create two identical
>> service accounts on both servers and use this account to login to SQL
>> Server. Then you would be able to use integrated security in your
>> connection string again.
>> --
>> Ekrem Önsoy
>>
>> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
>> news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
>>> We have a SQL Server 2000 database named acmedb on a Server2003
>>> machine named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When
>>> we run our asp.net 2.0 website on that same server we use the
>>> following connection string...
>>>
>>> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated
>>> Security=True
>>>
>>> This connection works fine. But now we want to run the website on
>>> SERVERTWO and access the database on SERVERONE. Using that same
>>> connection string we can't connect. we get: Login failed for user
>>> (null). Reason: Not associated with a trusted SQL Server connection.
>>> The server database is setup for both Windows and SQL server
>>> authentication so that is not the cause.
>>>
>>> The two servers are in two different workgroups but SERVERTWO can
>>> ping SERVERONE.
>>>
>>> I really don't want to change ownership of the db and have to pass
>>> passwords and UIDs in the connection string. Is there a way to make
>>> this work this way?
>>>
>>> Thanks,
>>> T
>>>
>>
>>
>>
>

DB Access to another server w/NT AUTHORITY

We have a SQL Server 2000 database named acmedb on a Server2003 machine
named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
asp.net 2.0 website on that same server we use the following connection
string...
Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
This connection works fine. But now we want to run the website on SERVERTWO
and access the database on SERVERONE. Using that same connection string we
can't connect. we get: Login failed for user (null). Reason: Not
associated with a trusted SQL Server connection. The server database is
setup for both Windows and SQL server authentication so that is not the
cause.
The two servers are in two different workgroups but SERVERTWO can ping
SERVERONE.
I really don't want to change ownership of the db and have to pass passwords
and UIDs in the connection string. Is there a way to make this work this
way?
Thanks,
TYou get that error because the service account that you use to login to your
SQL Server is not existed on both servers I believe.
You'd be able to connect if you'd be using a SQL Server Login, not NT
AUTHORITY as Windows Authentication. Then you'd need to change your
connection string to use uid and pwd instead of integrated security.
You could also use a method in which you'd create two identical service
accounts on both servers and use this account to login to SQL Server. Then
you would be able to use integrated security in your connection string
again.
Ekrem nsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
> We have a SQL Server 2000 database named acmedb on a Server2003 machine
> named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
> asp.net 2.0 website on that same server we use the following connection
> string...
> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
> This connection works fine. But now we want to run the website on
> SERVERTWO and access the database on SERVERONE. Using that same
> connection string we can't connect. we get: Login failed for user (null).
> Reason: Not associated with a trusted SQL Server connection. The server
> database is setup for both Windows and SQL server authentication so that
> is not the cause.
> The two servers are in two different workgroups but SERVERTWO can ping
> SERVERONE.
> I really don't want to change ownership of the db and have to pass
> passwords and UIDs in the connection string. Is there a way to make this
> work this way?
> Thanks,
> T
>|||Your problem has little to do with the ownership of the database on
SERVERONE. It is the APS.NET app. That is, the user account used to run the
ASP.NET application does not have access to the database on SERVERONE.
You need to configure the ASP.NET app correctly to access SQL Server.
The simple way would be to use SQL Server security, instead of Windows
security to log into SQL Server, as other other post suggested. So, you add
username/password to the ConnectionString.
However, if you need to use Windows authentication to access SQL Server, you
have to configure ASP.NET APP correctly. The ASP.NET APP cannot use local
account from SERVERTWO to access SQL Server on SERVERONE. Period (unless it
is peer-to-peer network).
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
> We have a SQL Server 2000 database named acmedb on a Server2003 machine
> named SERVERONE owned by NT AUTHORITY/NETWORK SERVICE. When we run our
> asp.net 2.0 website on that same server we use the following connection
> string...
> Data Source=SERVERONE;Initial Catalog=acmedb;Integrated Security=True
> This connection works fine. But now we want to run the website on
> SERVERTWO and access the database on SERVERONE. Using that same
> connection string we can't connect. we get: Login failed for user (null).
> Reason: Not associated with a trusted SQL Server connection. The server
> database is setup for both Windows and SQL server authentication so that
> is not the cause.
> The two servers are in two different workgroups but SERVERTWO can ping
> SERVERONE.
> I really don't want to change ownership of the db and have to pass
> passwords and UIDs in the connection string. Is there a way to make this
> work this way?
> Thanks,
> T
>|||Ekrem,
when you say...
You could also use a method in which you'd create two identical service
accounts on both servers and use this account to login to SQL Server. Then
you would be able to use integrated security in your connection string
again.
Can you give me an example of "Identical Service Accounts"?
Thanks,
T
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:D7784117-876B-4209-9EC8-9087194541E9@.microsoft.com...
> You get that error because the service account that you use to login to
> your SQL Server is not existed on both servers I believe.
> You'd be able to connect if you'd be using a SQL Server Login, not NT
> AUTHORITY as Windows Authentication. Then you'd need to change your
> connection string to use uid and pwd instead of integrated security.
> You could also use a method in which you'd create two identical service
> accounts on both servers and use this account to login to SQL Server. Then
> you would be able to use integrated security in your connection string
> again.
> --
> Ekrem nsoy
>
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
>|||For example, create an account like the following on your first server
username = xxx
pass = ooo
create another account on your second server
username = xxx
pass = ooo
Usernames and passwords are the same \ identical. And create a login in your
SQL Server for this account. Then you'll be able to login to your SQL Server
using this account and so using Windows Authentication.
Logon to your SERVERONE Windows using this xxx account and you'll be able to
login to your SQL Server under this account.
Ekrem nsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uchd5o0NIHA.5524@.TK2MSFTNGP05.phx.gbl...
> Ekrem,
> when you say...
> You could also use a method in which you'd create two identical service
> accounts on both servers and use this account to login to SQL Server. Then
> you would be able to use integrated security in your connection string
> again.
> Can you give me an example of "Identical Service Accounts"?
> Thanks,
> T
>
>
> "Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
> news:D7784117-876B-4209-9EC8-9087194541E9@.microsoft.com...
>|||so you are saying that the local account on the SERVERONE database server is
setup for Windows Authentication to the DB. right?
Then the connectionstring on the asp.net app running on SERVERTWO would not
have to include PWDand UID? What would that connectionstring look like?
Would it just have UID but no PWD?
T
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:62A50A48-512B-4F76-9351-1E16678A0108@.microsoft.com...
> For example, create an account like the following on your first server
> username = xxx
> pass = ooo
> create another account on your second server
> username = xxx
> pass = ooo
> Usernames and passwords are the same \ identical. And create a login in
> your SQL Server for this account. Then you'll be able to login to your SQL
> Server using this account and so using Windows Authentication.
> Logon to your SERVERONE Windows using this xxx account and you'll be able
> to login to your SQL Server under this account.
> --
> Ekrem nsoy
>
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uchd5o0NIHA.5524@.TK2MSFTNGP05.phx.gbl...
>|||So, are you saying that I can't do what I want to do? Whatever I do I will
need PWD and UID in the connectionstring on SERVERTWO?
T
"Norman Yuan" <NoAddress@.NoEmail.fake> wrote in message
news:eDQgkm0NIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Your problem has little to do with the ownership of the database on
> SERVERONE. It is the APS.NET app. That is, the user account used to run
> the ASP.NET application does not have access to the database on SERVERONE.
> You need to configure the ASP.NET app correctly to access SQL Server.
> The simple way would be to use SQL Server security, instead of Windows
> security to log into SQL Server, as other other post suggested. So, you
> add username/password to the ConnectionString.
> However, if you need to use Windows authentication to access SQL Server,
> you have to configure ASP.NET APP correctly. The ASP.NET APP cannot use
> local account from SERVERTWO to access SQL Server on SERVERONE. Period
> (unless it is peer-to-peer network).
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uOfpPftNIHA.5208@.TK2MSFTNGP04.phx.gbl...
>|||You'll use the same conn str. Just create the Windows user accounts and
create a login in sql server for this account and use this windows account
for your web application's windows session.
Ekrem nsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:ODhkLp1NIHA.5400@.TK2MSFTNGP04.phx.gbl...
> so you are saying that the local account on the SERVERONE database server
> is setup for Windows Authentication to the DB. right?
> Then the connectionstring on the asp.net app running on SERVERTWO would
> not have to include PWDand UID? What would that connectionstring look
> like? Would it just have UID but no PWD?
> T
>
> "Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
> news:62A50A48-512B-4F76-9351-1E16678A0108@.microsoft.com...
>|||Without knowing details on your system setup (domain network, peer-to-peer),
I cannot say more. Basically, you need some consideration on your ASP.NET
app side: which user account you want to use to run the ASP.NET app,
considersing the application's need to access resources locally or in the
network, and then from security point of view. Then you can tell if the
choosen account has necessary access to the SQL Server database in the other
computer.
So, I am not saying anything other than your ConnectionString shown in your
original post does not work. Again, cannt say more more before knowing more
details on your ASP.NET app configuration, network...
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:eV%230Gq1NIHA.6108@.TK2MSFTNGP03.phx.gbl...
> So, are you saying that I can't do what I want to do? Whatever I do I
> will need PWD and UID in the connectionstring on SERVERTWO?
> T
>
> "Norman Yuan" <NoAddress@.NoEmail.fake> wrote in message
> news:eDQgkm0NIHA.1184@.TK2MSFTNGP04.phx.gbl...
>|||I think your solution would only work if the two servers are in the same
domain - right?
T
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:E0E94E38-299C-4100-9DE8-16786A19000A@.microsoft.com...
> You'll use the same conn str. Just create the Windows user accounts and
> create a login in sql server for this account and use this windows account
> for your web application's windows session.
> --
> Ekrem nsoy
>
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:ODhkLp1NIHA.5400@.TK2MSFTNGP04.phx.gbl...
>

DB access revoked...

I have two servers (A & B) with 7 DBs and have created a
user on each server with DB_OWNER role.
When I restore a DB backup from one server A to B, the
user on server B, loses all its access to these 7 DBs from
a server level, though I can see that this user exists
from a DB perspective.
Any ideas why and how this can be corrected? Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0094_01C3810D.6E860260
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Check out sp_change_users_login in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Rob" <rhchin@.hotmail.com> wrote in message =news:0dfc01c3812e$29ce71e0$a001280a@.phx.gbl...
I have two servers (A & B) with 7 DBs and have created a user on each server with DB_OWNER role.
When I restore a DB backup from one server A to B, the user on server B, loses all its access to these 7 DBs from a server level, though I can see that this user exists from a DB perspective.
Any ideas why and how this can be corrected? Thanks.
--=_NextPart_000_0094_01C3810D.6E860260
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Check out sp_change_users_login in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Rob" wrote in message news:0dfc01c3812e$29=ce71e0$a001280a@.phx.gbl...I have two servers (A & B) with 7 DBs and have created a user on =each server with DB_OWNER role.When I restore a DB backup from one =server A to B, the user on server B, loses all its access to these 7 DBs from =a server level, though I can see that this user exists from a DB perspective.Any ideas why and how this can be corrected? Thanks.

---

--=_NextPart_000_0094_01C3810D.6E860260--

DB access question

I have SQL 2000 server with 2 databases A&B
What would cause the following to occur:
We are connecting ODBC and Querry Analyzer
When person 1 is in DB A
person 2 cannot connect to DB B
When person 2 is in DB B
person 1 cannot connect to DB A
Thanks in advanceWhat error message do you get when person 1 or 2 "can't connect"? Do you
get failed login messages in the SQL errorlog (do you have failed login
logging turned on?)? Have you run SQL profiler against your server when
you're trying to do this (monitoring a whole bunch of security events like
Successful Login, Failed Login, etc., etc)?
Cheers,
Mike
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:ODpsdQCzEHA.1404@.TK2MSFTNGP11.phx.gbl...
>I have SQL 2000 server with 2 databases A&B
> What would cause the following to occur:
> We are connecting ODBC and Querry Analyzer
> When person 1 is in DB A
> person 2 cannot connect to DB B
> When person 2 is in DB B
> person 1 cannot connect to DB A
> Thanks in advance
>sql

DB access question

I have SQL 2000 server with 2 databases A&B
What would cause the following to occur:
We are connecting ODBC and Querry Analyzer
When person 1 is in DB A
person 2 cannot connect to DB B
When person 2 is in DB B
person 1 cannot connect to DB A
Thanks in advance
What error message do you get when person 1 or 2 "can't connect"? Do you
get failed login messages in the SQL errorlog (do you have failed login
logging turned on?)? Have you run SQL profiler against your server when
you're trying to do this (monitoring a whole bunch of security events like
Successful Login, Failed Login, etc., etc)?
Cheers,
Mike
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:ODpsdQCzEHA.1404@.TK2MSFTNGP11.phx.gbl...
>I have SQL 2000 server with 2 databases A&B
> What would cause the following to occur:
> We are connecting ODBC and Querry Analyzer
> When person 1 is in DB A
> person 2 cannot connect to DB B
> When person 2 is in DB B
> person 1 cannot connect to DB A
> Thanks in advance
>

DB access question

I have SQL 2000 server with 2 databases A&B
What would cause the following to occur:
We are connecting ODBC and Querry Analyzer
When person 1 is in DB A
person 2 cannot connect to DB B
When person 2 is in DB B
person 1 cannot connect to DB A
Thanks in advanceWhat error message do you get when person 1 or 2 "can't connect"? Do you
get failed login messages in the SQL errorlog (do you have failed login
logging turned on?)? Have you run SQL profiler against your server when
you're trying to do this (monitoring a whole bunch of security events like
Successful Login, Failed Login, etc., etc)?
--
Cheers,
Mike
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:ODpsdQCzEHA.1404@.TK2MSFTNGP11.phx.gbl...
>I have SQL 2000 server with 2 databases A&B
> What would cause the following to occur:
> We are connecting ODBC and Querry Analyzer
> When person 1 is in DB A
> person 2 cannot connect to DB B
> When person 2 is in DB B
> person 1 cannot connect to DB A
> Thanks in advance
>

DB access from master page

I am using content and master pages. The content page has to query the database to get the master page name (among other things), which is done in 'Page_PreInit', and then the master page has to query the database to get some layout options, done in 'Page_Load'.

Is it possible for the master page to use the existing open connection, or am I forced to close the connection in the content page and open up a new connection in the master page? I've tried various things without success.

The second question is, does it matter? Will opening and closing two connections be much slower than opening and closing one?

In my opinion you should open a connection as late as possible and close it as early as possible. So I think you should not use the same connection, instead close the one as soon as its work gets done (dispose it or use theusing block) and then create a new one for the other work.

HTH,

Vivek

|||

Thanks.

I've since done some reading up on connection pools, and I agree with what you are saying. I assumed that opening and closing a connection twice would require twice as much work, but this is not the case.

We live and learn.

|||Yes, connection pooling reduces the number of times that new connections need to be opened. The pooler maintains ownership of the physical connection. Every time a connection is open/closed, the pooler does not actually open/close a connection, thus improves performance. For details you can take a look at this article:Using Connection Pooling
And this article gives some tips on connection pooling performance:
Tuning Up ADO.NET Connection Pooling in ASP.NET Applications

DB Access from different m/c

when i access the database from different DB server getting the following error

Msg 18452, Sev 14, State 1, Line 1 : Login failed for user ''. The user is not associated with a trusted SQL Server connection. [SQLSTATE 28000]

Thanks,
Ravinder

Your SQL Server is set to Windows Authentication only. You need to logon with a user which has granted access to the server. You can′t provide a userId and passwort in the connecting string because this will be ognored in the case of Windows Auth. only. Another option would be, if feasable for your to switch the authentication mode to mixed.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

DB access for web apps

I have custom .net web based application running on IIS 6 which has SQL2000
running in background, access to this application is based on UIDs and
password from "users" table in SQL, my question is regarding web.config
file... this file has user name and password that allow web application talk
to SQL db, what sql role should this account have in order to .net
application work corectly? db owner will do but I'm wondering this is too
much...
TIAFor a qick improvement, membership in the
db_datareader (can select all data from any user table in the database) and
db_datawriter (can modify any data in any user table in the database)
roles should be enough. Then you can study grainer permissions needed.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
news:5045D447-656F-46C8-A48E-29078FFAA294@.microsoft.com...
> I have custom .net web based application running on IIS 6 which has
SQL2000
> running in background, access to this application is based on UIDs and
> password from "users" table in SQL, my question is regarding web.config
> file... this file has user name and password that allow web application
talk
> to SQL db, what sql role should this account have in order to .net
> application work corectly? db owner will do but I'm wondering this is too
> much...
> TIA|||thanks for response, will this allow execute sp_ ?
"Dejan Sarka" wrote:

> For a qick improvement, membership in the
> db_datareader (can select all data from any user table in the database) an
d
> db_datawriter (can modify any data in any user table in the database)
> roles should be enough. Then you can study grainer permissions needed.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
> "Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
> news:5045D447-656F-46C8-A48E-29078FFAA294@.microsoft.com...
> SQL2000
> talk
>
>|||For stored procedures in your database, you will have to give an explicit
EXECUTE permission to this user. I you are talking about system procedures
to get some info, like sp_help, then the user will be able to execute them
without an explicit permission.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
news:F1EDD860-343A-421A-A303-2FBB25687147@.microsoft.com...[vbcol=seagreen]
> thanks for response, will this allow execute sp_ ?
> "Dejan Sarka" wrote:
>
and[vbcol=seagreen]
web.config[vbcol=seagreen]
application[vbcol=seagreen]
too[vbcol=seagreen]sql

DB Access for SQL Systems

How could I find a list of who has write access within my SQL2K5.
ThanksOn Apr 25, 12:06=A0am, "OA" <omr...@.verizon.net> wrote:
> How could I find =A0a list of who has write access within my SQL2K5.
> Thanks
Hi ,
You can use below commands for your requirement.
Run these procedure under the database for which you need the privs
info.
sp_helprotect
sp_helplogins
sp_helprolemember
Thanks
Ajay Rengunthwar
MCTS,MCDBA,MCAD

db access

I have connected to our group's sql 2000 server with NT
authentication but was not expecting it to work. I was
not expecting it to work because it's in a different
domain that my nt account, although I have the same nt
account name in both domains (e.g. I can log into either
domain with the same user and password but the passwords
are only the same because I sync them manually).
What I am wondering is how do I see what group my domain
account is a memeber of? I want to understand how this
account has access. It must be through some group
membership because my domain name is not listed in the
security folder of EM.
thanks"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:0ffe01c518f1$5e89a0b0$a501280a@.phx.gbl...
> I have connected to our group's sql 2000 server with NT
> authentication but was not expecting it to work. I was
> not expecting it to work because it's in a different
> domain that my nt account, although I have the same nt
> account name in both domains (e.g. I can log into either
> domain with the same user and password but the passwords
> are only the same because I sync them manually).
> What I am wondering is how do I see what group my domain
> account is a memeber of? I want to understand how this
> account has access. It must be through some group
> membership because my domain name is not listed in the
> security folder of EM.
The reason that this works, authentication pass-through enables your account
in one domain to pass the account name/password credentials from one domain
to another domain. As long as the account name/password are the same in both
domains, this will generally work (there are exceptions). As a test, change
your account password on domain "b", bet you get prompted (or get access
denied).
Steve

Datetime format regardless regional settings

Hi,

in tsql there is a common format for datetime (regardless regional settings)?

I use : 'mm/dd/yyyy' to access cols in database,

but someone says that the right one is: yyyy-mm-dd.

Any suggestion about that?

Thank a lot

I believe you want the ISO format -- 112; that is yymmdd. Can somebody double check this?

Dave

|||

yes dave.. 112 is the ansi unseperated date format.....

so best option is to get all ur date in this format: select convert(varchar, getdate(), 112)

|||

Hi,

I tried thid on nortwind DB (date are: dd/mm/yyyy)

select * from orders where orderdate < '19960704'

this does work, no data are retrieved,

but works for:

select * from orders where orderdate < '07/04/1996'

select * from orders where orderdate < '1996-07-04'

both select one row (if nortwind is the original one)

?

any suggestion?

All extract the same regardless the regional settings?

|||

Fast:

I tried all three queries against my copy of the northwind database. The lowest date that I have in the orders table is the date '07/04/1996'. For this reason, I do not get any rows returned with any of the select statements. However, when I change the "less than" operator to a "less than or equal to" operator I get the expected row. I am not getting the same results as you. I am afraid I can't help on this one. Sorry fo adding to confusion.

Dave

|||

hmm..works fine with me...the lowest date in northwind is..1996-07-04 00:00:00.000..

i get 0 rows for all the queries u mentiones...and 1 each when i use <= ..

neways...wat do u want to achieve exactally....see 112 is the ansi date format, which shud be used for date conversion and comparisons as its the standard....select convert(varchar(10), getdate(), 112) ...

yyyy-mm-dd +time is how sql server present it as..

|||

Sorry for having written too fast,

all three select retrieve 1 record if date is minor than 5 july 1996,

I have tried all queries with several regional settings and everything work.

If anyone has other suggestion, is welcome

Besides in internet there are this interestings articles:

http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-delimit/format-dates-for-database-entry.html

and

http://www.karaszi.com/SQLServer/info_datetime.asp

Thank

Datetime field - MS Access vs SQL Server

Hi all,
We were using MS Access as database and VB as frontend. Now we need
to support the database SQL Server also. We have following code which
works fine for MS Access.
For i = 0 To rsRecordset.Fields.Count - 1
Set objField = rsRecordset.Fields(i)
If (objField.Attributes And adFldIsNullable) =
adFldIsNullable Then
Select Case objField.Type
case adDate, adDBDate, adDBTime, adDBTimeStamp
If IsNull(objField.Value) Then
objField.Value = "#1/1/1900#"
--(1)
End If
end select
end if
next i
The select statement for that recordset is like this,
"Select max(Result_Date) as max_date from TestTable"
It was working fine with MS Access.
While executing statement(1) in SQL Server, it is throwing the error
"Multiple-step operation generated errors. Check each status value." I
found that the problem is using "max" function in the query(for that
field).
Can any one help for a work around for the above code to work in this
scenario for SQL Server.
Thanks in Advance.
Swapna.At a quick glance i would say get rid of the pound (#) signs in the date
http://sqlservercode.blogspot.com/
"swapna_munukoti@.yahoo.co.in" wrote:

> Hi all,
> We were using MS Access as database and VB as frontend. Now we need
> to support the database SQL Server also. We have following code which
> works fine for MS Access.
> For i = 0 To rsRecordset.Fields.Count - 1
> Set objField = rsRecordset.Fields(i)
> If (objField.Attributes And adFldIsNullable) =
> adFldIsNullable Then
> Select Case objField.Type
> case adDate, adDBDate, adDBTime, adDBTimeStamp
> If IsNull(objField.Value) Then
> objField.Value = "#1/1/1900#"
> --(1)
> End If
> end select
> end if
> next i
> The select statement for that recordset is like this,
> "Select max(Result_Date) as max_date from TestTable"
> It was working fine with MS Access.
> While executing statement(1) in SQL Server, it is throwing the error
> "Multiple-step operation generated errors. Check each status value." I
> found that the problem is using "max" function in the query(for that
> field).
> Can any one help for a work around for the above code to work in this
> scenario for SQL Server.
> Thanks in Advance.
> Swapna.
>|||> objField.Value = "#1/1/1900#"
How about
objField.Value = CDate("1900-01-01")|||All these things are working if the fleld is a direct one, as in this
query, "Select Result_Date as max_date from TestTable".
None of the above suggestions are working if the query has any field
with function like max() in it.|||You're trying to UPDATE a recordset that uses aggregates?
Have you considered using an UPDATE statement or a stored procedure?
<swapna_munukoti@.yahoo.co.in> wrote in message
news:1127364048.999418.224150@.g43g2000cwa.googlegroups.com...
> All these things are working if the fleld is a direct one, as in this
> query, "Select Result_Date as max_date from TestTable".
> None of the above suggestions are working if the query has any field
> with function like max() in it.
>|||<swapna_munukoti@.yahoo.co.in> wrote in message
news:1127364048.999418.224150@.g43g2000cwa.googlegroups.com...
> All these things are working if the fleld is a direct one, as in this
> query, "Select Result_Date as max_date from TestTable".
> None of the above suggestions are working if the query has any field
> with function like max() in it.
You cannot update a derived field of any sort, values returned by aggregate
functions included.
I'd be very much surprised to find that this truly worked with Jet -- I'd
easily believe it neglected to throw an error, but what row would it update?
-Mark

DateTime

PLZ help me!!!
i got to make a access frontend for a MSSQL server.
i need to select some stuff per week how can i make thet? PLZZ HELP I GOT
TO KNOW IT TOMAROW
TNX
Peter
If by "select some stuff per week" you mean calculate aggregates (sum,
min, max, count) per week, you can do something like this:
declare @.baseSunday datetime
set @.baseSunday = '19000107'
select
dateadd(week, datediff(week, @.baseSunday, OrderDate), @.baseSunday)
as WeekBeginning,
count(OrderID) as numOrdersThisWeek
from Northwind..Orders
group by dateadd(week, datediff(week, @.baseSunday, OrderDate), @.baseSunday)
order by dateadd(week, datediff(week, @.baseSunday, OrderDate), @.baseSunday)
go
If you want result rows for weeks that have no corresponding data in
your table, you can create an auxiliary table with all the Sundays you
would need and do
select
Sunday as WeekBeginning,
count(OrderID) as numOrdersThisWeek
from SundayTable left outer join yourDataTable
on yourDataTable.OrderDate >= Sunday
and yourDataTable.OrderDate < DateAdd(week, 1, Sunday)
group by Sunday
order by Sunday
A search of groups.google.com for sqlserver+group+week will probably
give you some other ideas.
Steve Kass
Drew University
Crazy Pete wrote:

>PLZ help me!!!
>i got to make a access frontend for a MSSQL server.
>i need to select some stuff per week how can i make thet? PLZZ HELP I GOT
>TO KNOW IT TOMAROW
>TNX
>Peter
>
>

DateSerial in Access to SQL

Hi,
I need to convert this Access code to a Sql Case statement, but I'm not sure
exactly how...
IIf([Conversion Detail Table]!TXTDT<>0,DateSerial((Int(Left([Conversion
Detail Table]!TXTDT,4))),(Int(Mid([Conversion Detail
Table]!TXTDT,5,2))),(Int(Right([Conversi
on Detail
Table]!TXTDT,2)))),#10/1/2004#)
Can anyone help, thank you very much!
PatriceTry,
select
case when rtrim(TXTDT) != '0' then cast(left(8, TXTDT) as datetime) else
cast('20041001' as datetime) end
from
[Conversion Detail Table]
go
AMB
"Patrice" wrote:

> Hi,
> I need to convert this Access code to a Sql Case statement, but I'm not su
re
> exactly how...
>
> IIf([Conversion Detail Table]!TXTDT<>0,DateSerial((Int(Left([Conversion
> Detail Table]!TXTDT,4))),(Int(Mid([Conversion Detail
> Table]!TXTDT,5,2))),(Int(Right([Conversi
on Detail
> Table]!TXTDT,2)))),#10/1/2004#)
> Can anyone help, thank you very much!
> Patrice
>

Dates prior to 1753-01-01

Hello,

I have an Access DB with a column [date] holding dates from present day back
to around year 1702, and I am trying to run DTS Import with this MDB, which
fails due to a conversion error.

MSDN says the datetime data type only allows 1753-01-01 to 1900-01-01, thus
I am under the impression having the [date] field as varchar is the only way
to store the dates.

My questions are:

a) Can dates prior to 1753-01-01 be stored in a date column?

b) If dates are in a column of type varchar (or similar) is it possible to
sort them chronologically with T-SQL ?

c) 1753-01-01 seems particularly arbitrary, any reason for this?

Many thanks for any help, it's driving me mad!
Johna) No

b) Yes. If you store them as most-siginificant digits first (YYYYMMDD) you
can safely sort as for any other VARCHAR.

c) 1752 was when Britain and its colonies (including parts of America)
switched to the Gregorian calendar. The problem with much earlier dates is
that date validation, arithmetic and comparisons don't make sense unless you
put the date in the context of a particular calendar and location. For
example, Access allows a date of 1752-09-04 - a date which never happened at
all in Britain but was valid for other parts of the world. Rather than try
to make sense of this SQL Server takes the easy way out, from a US point of
view, by taking 1753 as its calendar starting date.

Take a look at:
http://www.tondering.dk/claus/calendar.html

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> c) 1752 was when Britain and its colonies (including parts of America)
> switched to the Gregorian calendar. The problem with much earlier dates
> is that date validation, arithmetic and comparisons don't make sense
> unless you put the date in the context of a particular calendar and
> location.

From a pedantic point of view, this applies to later date as well. The
Orthodox world did not change until around 1918. That's how got an
October revolution that is celebrated in November. ("Is?", the next pedant
may say now. The sad story is that there are still some people thinks
that are reason to celebrate this event.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Google up a hisotry of the calendar, it is fun!

However, for your problem, Google up "Julian dates". It is a large
integer used by astronomers to avoid calendar problems. It can be
converted back to the Common Era (aka Gregorian or Christian) calendar
with a bit of code.|||Cheers for the help.
John

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:zqudnREkRYdsb0_fRVn-gg@.giganews.com...
> a) No
> b) Yes. If you store them as most-siginificant digits first (YYYYMMDD) you
> can safely sort as for any other VARCHAR.
> c) 1752 was when Britain and its colonies (including parts of America)
> switched to the Gregorian calendar. The problem with much earlier dates is
> that date validation, arithmetic and comparisons don't make sense unless
you
> put the date in the context of a particular calendar and location. For
> example, Access allows a date of 1752-09-04 - a date which never happened
at
> all in Britain but was valid for other parts of the world. Rather than try
> to make sense of this SQL Server takes the easy way out, from a US point
of
> view, by taking 1753 as its calendar starting date.
> Take a look at:
> http://www.tondering.dk/claus/calendar.html
> --
> David Portas
> SQL Server MVP
> --|||Pedants' Calendar:
http://www.douglasadams.com/dna/pedants.html

;-)

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Pedants' Calendar:
> http://www.douglasadams.com/dna/pedants.html
> ;-)

Hehe, that was a good one!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

DateAdd function

Hi guys,
In Access u can do something like the following in ur query:

SELECT...
WHERE (weekendingEmp.WeekEnding)>(DateAdd("ww",-3,Now()))

Now() function will get the current system date.
1st parameter which is "ww" means that it will add number of weeks to the current system date according to number specified by the 2nd parameter..
so basically, this query will use DateAdd function as part of WHERE statement and collect data from 3 weeks ago up to today...

unfortunately, i can't use Access object anymore and i need to use ADO.. i believe regular SQL statement doesn't understand "DateAdd" function so my question is.. does anyone kno how i can come around this problem w/out using DateAdd function?

thanksIt depends on the DB you are using. If you are using SQL Server, you can do the exact same thing with:

SELECT...
WHERE (weekendingEmp.WeekEnding)>(DATEADD(ww,-3,GETDATE()))