db error: named

Showing posts with label named. 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

Thursday, March 8, 2012

Datetime Conversion

In a table I have a column named DTTM_RCD_ADDED of type varchar with a length of 14. The format of the column is:
YYYYMMDDHHMMSS.
What I would like to do is output:
DD-MM-YYYY HH:MM:SS

I have tried to cast and convert this column but nothing is working. Please help.

Using SQL Server 2000The closest thing that I have is this statement:

CONVERT(smalldatetime, SUBSTRING(C.DTTM_RCD_ADDED, 1, 8), 120)

The problem with this is that it has the HH:MM:SS but they are all zeros. When I use
CONVERT(smalldatetime, SUBSTRING(C.DTTM_RCD_ADDED, 1, 14), 120)
or completly get rid of the substring part I get an error.

Wednesday, March 7, 2012

datetime

Hello,
I have a table with a colume named orderTime.
Because of the size of the DB, I need to make sure all the data is
stream lined. This means that if I only need the time, I need to just
store the time. Unfortunately, datetime stores 1900-01-01 as a data
with the time which is a string from a VB app.
Anyway to insert just the time? I could change the column to a string,
but I rather not do that because I'm not sure if I can order by time
properly latter.> Anyway to insert just the time?
No, SQL Server does not have a date only or time only datatype. But can't
you just always ignore 1900-01-01?
Some workarounds here, but they're not pretty.
http://www.aspfaq.com/2206
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||> No, SQL Server does not have a date only or time only datatype. But can't
> you just always ignore 1900-01-01?
> Some workarounds here, but they're not pretty.
> http://www.aspfaq.com/2206
Aaron,
Thanks. Yes, I can ignore them. The issue though is that because the
sheer amount of data I'm storing, I need to only insert data that is
needed. I guess I will just have to work around this. Thanks.|||> Thanks. Yes, I can ignore them. The issue though is that because the
> sheer amount of data I'm storing, I need to only insert data that is
> needed.
? If you store a date in a DATETIME or SMALLDATETIME, regardless if you
include all information, it still occupies 4 or 8 bytes respectively. So
"not inserting" the portion you don't need is not going to save you any
space.|||I don't loose any accuracy if I were to store that time as a char(8) and
then just do a CAST?
Which will take up less space?
I found this in Books Online,
When the collation code page uses double-byte characters, the storage
size is still n bytes. Depending on the character string, the storage
size of n bytes may be less than n characters.
Doesn't seem to give a definitive answer.|||SMALLDATETIME (4 bytes) will take up less space than a CHAR(8) (8 bytes).
Plus, you lose effectiveness of indexes and have to convert for every single
query.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Won Lee" <noemail@.nospam.com> wrote in message
news:#CfX$vIAEHA.2480@.TK2MSFTNGP12.phx.gbl...
> I don't loose any accuracy if I were to store that time as a char(8) and
> then just do a CAST?
> Which will take up less space?
> I found this in Books Online,
> When the collation code page uses double-byte characters, the storage
> size is still n bytes. Depending on the character string, the storage
> size of n bytes may be less than n characters.
>
> Doesn't seem to give a definitive answer.
>

datetime

Hello,
I have a table with a colume named orderTime.
Because of the size of the DB, I need to make sure all the data is
stream lined. This means that if I only need the time, I need to just
store the time. Unfortunately, datetime stores 1900-01-01 as a data
with the time which is a string from a VB app.
Anyway to insert just the time? I could change the column to a string,
but I rather not do that because I'm not sure if I can order by time
properly latter.> Anyway to insert just the time?
No, SQL Server does not have a date only or time only datatype. But can't
you just always ignore 1900-01-01?
Some workarounds here, but they're not pretty.
http://www.aspfaq.com/2206
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||> No, SQL Server does not have a date only or time only datatype. But can't
> you just always ignore 1900-01-01?
> Some workarounds here, but they're not pretty.
> http://www.aspfaq.com/2206
Aaron,
Thanks. Yes, I can ignore them. The issue though is that because the
sheer amount of data I'm storing, I need to only insert data that is
needed. I guess I will just have to work around this. Thanks.|||> Thanks. Yes, I can ignore them. The issue though is that because the
> sheer amount of data I'm storing, I need to only insert data that is
> needed.
? If you store a date in a DATETIME or SMALLDATETIME, regardless if you
include all information, it still occupies 4 or 8 bytes respectively. So
"not inserting" the portion you don't need is not going to save you any
space.|||I don't loose any accuracy if I were to store that time as a char(8) and
then just do a CAST?
Which will take up less space?
I found this in Books Online,
When the collation code page uses double-byte characters, the storage
size is still n bytes. Depending on the character string, the storage
size of n bytes may be less than n characters.
Doesn't seem to give a definitive answer.|||SMALLDATETIME (4 bytes) will take up less space than a CHAR(8) (8 bytes).
Plus, you lose effectiveness of indexes and have to convert for every single
query.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Won Lee" <noemail@.nospam.com> wrote in message
news:#CfX$vIAEHA.2480@.TK2MSFTNGP12.phx.gbl...
> I don't loose any accuracy if I were to store that time as a char(8) and
> then just do a CAST?
> Which will take up less space?
> I found this in Books Online,
> When the collation code page uses double-byte characters, the storage
> size is still n bytes. Depending on the character string, the storage
> size of n bytes may be less than n characters.
>
> Doesn't seem to give a definitive answer.
>

Friday, February 17, 2012

DateDiff Function

Hi all !!!!
I am a new bee for programming, my question may be simple but i dont know how to do it...My senario is I have four column named DISCOUNT CODE, DATE ACTIVATED, DATE DEAVTIVATE, IS ACTIVE... So when i use the DISCOUNT CODE the DATE ACTIVATED should be todays date and the DATE DEACTIVATE should be exactly one year from the date activated...and in the IS ACTIVE column the value should be 1 untill the code is active...and after one year the value should be 0...

could some pls guide me how to write a stored procedure for this...Thanks in advance..

Regards,
Rahul

Quote:

Originally Posted by mibbsin

could some pls guide me how to write a stored procedure for this...Thanks in advance..

Regards,
Rahul

if you're inserting into a table

insert into DISCOUNTTABLE (DISCOUNT CODE, DATE ACTIVATED, DATE DEAVTIVATE, IS ACTIVE) values (newdiscountcode, getdate(), dateadd(yy,1, getdate(), 1)...

now, you need a batch to run every 12MN to:

UPDATE DISCOUNTTABLE
set ISACTIVE = 0
where DATE DEAVTIVATE = getdate()

this code was not checked..it's more a pseudocode than anything...but i hope you got the idea

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

Thursday, March 8, 2012

Datetime Conversion

Wednesday, March 7, 2012

datetime

datetime

Friday, February 17, 2012

DateDiff Function

db error

Blog Archive

About Me