db error: authority

Showing posts with label authority. Show all posts

Tuesday, March 27, 2012

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

Sunday, February 19, 2012

DateDiff returns odd result

I have a table of addresses for residents in a housing authority, and these
addresses may change so I need to find the first move in date and subtract it
from today's date to get the length of residency. Here is my query:
select distinct a1.HouseholdNbr,
(SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
a1.householdNbr = a2.householdNbr) as MoveInDate,
DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
from tblResidentAddresses a1
where a1.householdnbr < 99000
order by HouseholdNbr
The length of years residency is correct except for years that are 2004 or
2005. Here is a sample result:
MoveinDate Residency
01/13/20014
08/08/200412
02/21/19978
02/19/198817
07/12/200410
01/17/19978
06/22/199411
06/27/19978
05/24/20046
01/20/20014
07/12/20046
06/01/200424
02/20/200413
01/01/200512
11/18/19978
04/19/20014
08/25/19996
03/22/200429
12/01/199411
Any suggestions why 2004 and 2005 produce wrong results? Thanks
Hi
Posting ddl and example data would help see
http://www.aspfaq.com/etiquette.asp?id=5006. I believe the problem is related
to your function dbo.dateString, try adding it to the datediff MIN()
statement as using datetimes is fine:
CREATE TABLE tblResidentAddresses ( householdnbr int not null
identity(1,1), MoveinDate datetime not null)
INSERT INTO tblResidentAddresses ( MoveinDate )
SELECT '20010113'
UNION ALL SELECT '20040808'
UNION ALL SELECT '19970221'
UNION ALL SELECT '19880219'
UNION ALL SELECT '20040712'
UNION ALL SELECT '19970117'
UNION ALL SELECT '19940622'
UNION ALL SELECT '19970527'
UNION ALL SELECT '20040524'
UNION ALL SELECT '20010120'
UNION ALL SELECT '20040712'
UNION ALL SELECT '20040601'
UNION ALL SELECT '20040220'
UNION ALL SELECT '20050101'
UNION ALL SELECT '19971118'
UNION ALL SELECT '20010419'
UNION ALL SELECT '19990825'
UNION ALL SELECT '20040322'
UNION ALL SELECT '19941201'
SELECT DISTINCT a1.householdnbr,
CONVERT(char(10),(SELECT MIN(MoveInDate) FROM tblResidentAddresses a2 WHERE
a1.householdnbr = a2.householdnbr),110) AS MoveInDate,
DATEDIFF(yy,(SELECT MIN(MoveInDate) FROM tblResidentAddresses a3 WHERE
a1.householdnbr = a3.householdnbr), GETDATE()) AS Residency
FROM tblResidentAddresses a1
WHERE a1.householdnbr < 99000
ORDER BY a1.householdnbr
Seems fine:
householdnbr MoveInDate Residency
-- -- --
1 01-13-2001 4
2 08-08-2004 1
3 02-21-1997 8
4 02-19-1988 17
5 07-12-2004 1
6 01-17-1997 8
7 06-22-1994 11
8 05-27-1997 8
9 05-24-2004 1
10 01-20-2001 4
11 07-12-2004 1
12 06-01-2004 1
13 02-20-2004 1
14 01-01-2005 0
15 11-18-1997 8
16 04-19-2001 4
17 08-25-1999 6
18 03-22-2004 1
19 12-01-1994 11
(19 row(s) affected)
John
"DLS" wrote:

> I have a table of addresses for residents in a housing authority, and these
> addresses may change so I need to find the first move in date and subtract it
> from today's date to get the length of residency. Here is my query:
> select distinct a1.HouseholdNbr,
> (SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
> a1.householdNbr = a2.householdNbr) as MoveInDate,
> DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
> a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
> from tblResidentAddresses a1
> where a1.householdnbr < 99000
> order by HouseholdNbr
> The length of years residency is correct except for years that are 2004 or
> 2005. Here is a sample result:
> MoveinDate Residency
> 01/13/20014
> 08/08/200412
> 02/21/19978
> 02/19/198817
> 07/12/200410
> 01/17/19978
> 06/22/199411
> 06/27/19978
> 05/24/20046
> 01/20/20014
> 07/12/20046
> 06/01/200424
> 02/20/200413
> 01/01/200512
> 11/18/19978
> 04/19/20014
> 08/25/19996
> 03/22/200429
> 12/01/199411
> Any suggestions why 2004 and 2005 produce wrong results? Thanks

DateDiff returns odd result

I have a table of addresses for residents in a housing authority, and these
addresses may change so I need to find the first move in date and subtract it
from today's date to get the length of residency. Here is my query:
select distinct a1.HouseholdNbr,
(SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
a1.householdNbr = a2.householdNbr) as MoveInDate,
DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
from tblResidentAddresses a1
where a1.householdnbr < 99000
order by HouseholdNbr
The length of years residency is correct except for years that are 2004 or
2005. Here is a sample result:
MoveinDate Residency
01/13/2001 4
08/08/2004 12
02/21/1997 8
02/19/1988 17
07/12/2004 10
01/17/1997 8
06/22/1994 11
06/27/1997 8
05/24/2004 6
01/20/2001 4
07/12/2004 6
06/01/2004 24
02/20/2004 13
01/01/2005 12
11/18/1997 8
04/19/2001 4
08/25/1999 6
03/22/2004 29
12/01/1994 11
Any suggestions why 2004 and 2005 produce wrong results? ThanksHi
Posting ddl and example data would help see
http://www.aspfaq.com/etiquette.asp?id=5006. I believe the problem is related
to your function dbo.dateString, try adding it to the datediff MIN()
statement as using datetimes is fine:
CREATE TABLE tblResidentAddresses ( householdnbr int not null
identity(1,1), MoveinDate datetime not null)
INSERT INTO tblResidentAddresses ( MoveinDate )
SELECT '20010113'
UNION ALL SELECT '20040808'
UNION ALL SELECT '19970221'
UNION ALL SELECT '19880219'
UNION ALL SELECT '20040712'
UNION ALL SELECT '19970117'
UNION ALL SELECT '19940622'
UNION ALL SELECT '19970527'
UNION ALL SELECT '20040524'
UNION ALL SELECT '20010120'
UNION ALL SELECT '20040712'
UNION ALL SELECT '20040601'
UNION ALL SELECT '20040220'
UNION ALL SELECT '20050101'
UNION ALL SELECT '19971118'
UNION ALL SELECT '20010419'
UNION ALL SELECT '19990825'
UNION ALL SELECT '20040322'
UNION ALL SELECT '19941201'
SELECT DISTINCT a1.householdnbr,
CONVERT(char(10),(SELECT MIN(MoveInDate) FROM tblResidentAddresses a2 WHERE
a1.householdnbr = a2.householdnbr),110) AS MoveInDate,
DATEDIFF(yy,(SELECT MIN(MoveInDate) FROM tblResidentAddresses a3 WHERE
a1.householdnbr = a3.householdnbr), GETDATE()) AS Residency
FROM tblResidentAddresses a1
WHERE a1.householdnbr < 99000
ORDER BY a1.householdnbr
Seems fine:
householdnbr MoveInDate Residency
-- -- --
1 01-13-2001 4
2 08-08-2004 1
3 02-21-1997 8
4 02-19-1988 17
5 07-12-2004 1
6 01-17-1997 8
7 06-22-1994 11
8 05-27-1997 8
9 05-24-2004 1
10 01-20-2001 4
11 07-12-2004 1
12 06-01-2004 1
13 02-20-2004 1
14 01-01-2005 0
15 11-18-1997 8
16 04-19-2001 4
17 08-25-1999 6
18 03-22-2004 1
19 12-01-1994 11
(19 row(s) affected)
John
"DLS" wrote:
> I have a table of addresses for residents in a housing authority, and these
> addresses may change so I need to find the first move in date and subtract it
> from today's date to get the length of residency. Here is my query:
> select distinct a1.HouseholdNbr,
> (SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
> a1.householdNbr = a2.householdNbr) as MoveInDate,
> DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
> a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
> from tblResidentAddresses a1
> where a1.householdnbr < 99000
> order by HouseholdNbr
> The length of years residency is correct except for years that are 2004 or
> 2005. Here is a sample result:
> MoveinDate Residency
> 01/13/2001 4
> 08/08/2004 12
> 02/21/1997 8
> 02/19/1988 17
> 07/12/2004 10
> 01/17/1997 8
> 06/22/1994 11
> 06/27/1997 8
> 05/24/2004 6
> 01/20/2001 4
> 07/12/2004 6
> 06/01/2004 24
> 02/20/2004 13
> 01/01/2005 12
> 11/18/1997 8
> 04/19/2001 4
> 08/25/1999 6
> 03/22/2004 29
> 12/01/1994 11
> Any suggestions why 2004 and 2005 produce wrong results? Thanks

DateDiff returns odd result

I have a table of addresses for residents in a housing authority, and these
addresses may change so I need to find the first move in date and subtract i
t
from today's date to get the length of residency. Here is my query:
select distinct a1.HouseholdNbr,
(SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
a1.householdNbr = a2.householdNbr) as MoveInDate,
DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
from tblResidentAddresses a1
where a1.householdnbr < 99000
order by HouseholdNbr
The length of years residency is correct except for years that are 2004 or
2005. Here is a sample result:
MoveinDate Residency
01/13/2001 4
08/08/2004 12
02/21/1997 8
02/19/1988 17
07/12/2004 10
01/17/1997 8
06/22/1994 11
06/27/1997 8
05/24/2004 6
01/20/2001 4
07/12/2004 6
06/01/2004 24
02/20/2004 13
01/01/2005 12
11/18/1997 8
04/19/2001 4
08/25/1999 6
03/22/2004 29
12/01/1994 11
Any suggestions why 2004 and 2005 produce wrong results? ThanksHi
Posting ddl and example data would help see
http://www.aspfaq.com/etiquette.asp?id=5006. I believe the problem is relate
d
to your function dbo.dateString, try adding it to the datediff MIN()
statement as using datetimes is fine:
CREATE TABLE tblResidentAddresses ( householdnbr int not null
identity(1,1), MoveinDate datetime not null)
INSERT INTO tblResidentAddresses ( MoveinDate )
SELECT '20010113'
UNION ALL SELECT '20040808'
UNION ALL SELECT '19970221'
UNION ALL SELECT '19880219'
UNION ALL SELECT '20040712'
UNION ALL SELECT '19970117'
UNION ALL SELECT '19940622'
UNION ALL SELECT '19970527'
UNION ALL SELECT '20040524'
UNION ALL SELECT '20010120'
UNION ALL SELECT '20040712'
UNION ALL SELECT '20040601'
UNION ALL SELECT '20040220'
UNION ALL SELECT '20050101'
UNION ALL SELECT '19971118'
UNION ALL SELECT '20010419'
UNION ALL SELECT '19990825'
UNION ALL SELECT '20040322'
UNION ALL SELECT '19941201'
SELECT DISTINCT a1.householdnbr,
CONVERT(char(10),(SELECT MIN(MoveInDate) FROM tblResidentAddresses a2 WHERE
a1.householdnbr = a2.householdnbr),110) AS MoveInDate,
DATEDIFF(yy,(SELECT MIN(MoveInDate) FROM tblResidentAddresses a3 WHERE
a1.householdnbr = a3.householdnbr), GETDATE()) AS Residency
FROM tblResidentAddresses a1
WHERE a1.householdnbr < 99000
ORDER BY a1.householdnbr
Seems fine:
householdnbr MoveInDate Residency
-- -- --
1 01-13-2001 4
2 08-08-2004 1
3 02-21-1997 8
4 02-19-1988 17
5 07-12-2004 1
6 01-17-1997 8
7 06-22-1994 11
8 05-27-1997 8
9 05-24-2004 1
10 01-20-2001 4
11 07-12-2004 1
12 06-01-2004 1
13 02-20-2004 1
14 01-01-2005 0
15 11-18-1997 8
16 04-19-2001 4
17 08-25-1999 6
18 03-22-2004 1
19 12-01-1994 11
(19 row(s) affected)
John
"DLS" wrote:

> I have a table of addresses for residents in a housing authority, and thes
e
> addresses may change so I need to find the first move in date and subtract
it
> from today's date to get the length of residency. Here is my query:
> select distinct a1.HouseholdNbr,
> (SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
> a1.householdNbr = a2.householdNbr) as MoveInDate,
> DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
> a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
> from tblResidentAddresses a1
> where a1.householdnbr < 99000
> order by HouseholdNbr
> The length of years residency is correct except for years that are 2004 or
> 2005. Here is a sample result:
> MoveinDate Residency
> 01/13/2001 4
> 08/08/2004 12
> 02/21/1997 8
> 02/19/1988 17
> 07/12/2004 10
> 01/17/1997 8
> 06/22/1994 11
> 06/27/1997 8
> 05/24/2004 6
> 01/20/2001 4
> 07/12/2004 6
> 06/01/2004 24
> 02/20/2004 13
> 01/01/2005 12
> 11/18/1997 8
> 04/19/2001 4
> 08/25/1999 6
> 03/22/2004 29
> 12/01/1994 11
> Any suggestions why 2004 and 2005 produce wrong results? Thanks

Tuesday, March 27, 2012

DB Access to another server w/NT AUTHORITY

DB Access to another server w/NT AUTHORITY

DB Access to another server w/NT AUTHORITY

Sunday, February 19, 2012

DateDiff returns odd result

DateDiff returns odd result

DateDiff returns odd result

db error

Blog Archive

About Me