db error: service

Showing posts with label service. 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 entry for querying analysis service cube

Hi everybody,

I have two problems while using a analysis service cube as data source for a reporting service report.

1.) I've an individual time dimension which has day entries in the standard date format "mm/dd/yyyy". When using an parametric entry for the date hierachy the reporting offers me all entries as a list (some 1000 entries). Looking under report parameters I recognized that the input parameter is listed as of the type string. However I know that the underlying field and as well the hierachy in the cube is of the format datetime. Change it to datetime causes the reporting service to fail with the error message:

An error occured during local report processing.
The property 'ValidValues' of report parameter 'DIM...' doesn't have the expected type.

How can I use the parameter in the format datetime to restrict the time dimension? ...so that I can select the date over the calendar function.

2.) I have another dimension with the hierachy cycle which has the string format "year-month". I would like to use the selection of the date hierachy to create the restriction on the cycle hierachy. I.e. entering '01/16/2007' on the time dimension should write the value '2007-01' to a parameter which is then used to restrict the cycle hierachy. Experimenting with report parameters always caused the error message:

An error occured during local report processing.
An error has occured during report processing.
Query execution failed for data set 'DIM...'.
Query (1,453) The restriction by the CONSTRAINED-flag in the STRTOSET-function has been violated.

As I only allow single value entries I thought about changing the STRTOSET command in the underlying MDX query into STRTOMEMBER. However this didn't solve the problem.

How can I create an input for a restriction on a dimension based on a parameter with a self constructed string?

Thanks,

StSt

However I know that the underlying field and as well the hierachy in the cube is of the format datetime

Each member in your Time dimension is identified using the following format [DimensionName].[AttributeHierarchyName].&[MemberKey]. This is the format that the generated parameter query uses. You can use this format to apply a fiter and limit the members shown. The Report Builder could help you to understand how to set the filter. Alternatively, you can set the Value property of the Date dimension key to the underlying field of DateTime type. However, each SSRS parameter can have only two values (label and value). To pass the selected value to the main query you need to resolve it to a valid member (again [DimensionName].[AttributeHierarchyName].&[MemberKey]). So, it may be more convenient to stick to this format as the parameter value.

|||Thanks this was of help ...even so I don't like the idea of constructing the member representation of the analysis service but it works

|||can you tell me exactly how you resolved this? thanks,

Datetime entry for querying analysis service cube

Hi everybody,

I have two problems while using a analysis service cube as data source for a reporting service report.

An error occured during local report processing.
The property 'ValidValues' of report parameter 'DIM...' doesn't have the expected type.

How can I use the parameter in the format datetime to restrict the time dimension? ...so that I can select the date over the calendar function.

As I only allow single value entries I thought about changing the STRTOSET command in the underlying MDX query into STRTOMEMBER. However this didn't solve the problem.

How can I create an input for a restriction on a dimension based on a parameter with a self constructed string?

Thanks,

StSt

However I know that the underlying field and as well the hierachy in the cube is of the format datetime

|||Thanks this was of help ...even so I don't like the idea of constructing the member representation of the analysis service but it works

|||can you tell me exactly how you resolved this? thanks,

Friday, February 17, 2012

DateDiff Issue - Need some coding Assistance

I need some coding assistance! I need the number of Days between the Date of Service (pv.visit) and the First Filed Date (pv.FirstFiledDate). Currently, this code seperates out the Electronic Claims and Paper Claims. Can anyone look at this code and let me know where I went wrong?! Any help is greatly appreciated.

set nocount on

declare @.startdate datetime,
@.enddate datetime,
@.ticketnumber varchar(20)

set @.ticketnumber = CAST(NULL as VARCHAR(20))
set @.startdate = ISNULL(NULL,'1/1/1900')
set @.enddate = DATEADD(DAY,1,ISNULL(NULL,'1/1/3000'))

SELECT ic.ListName AS CarrierName,
ic.address1 as CarrierAddress,
ic.city as CarrierCity,
ic.state as CarrierState,
ic.zip as CarrierZip,
icc.ClaimPayerId,
pv.Ticketnumber,
pv.visit as DateOfService,
pp.last+', '+pp.first as PatientName,
pp.PatientID,
ec.Charges as VisitChargesFiled,
ec.Procedures as VisitProceduresFiled,
pv.FirstFiledDate AS FirstFiledDate,
-- DATEDIFF(day,pv.Visit,pv.firstfileddate)AS Days,
ecf.FileTransmitted,
CAST(NULL as DATETIME) as ClaimPrinted,
ecf.FiledBy,
ecf.SubmissionNumber,
ecf.name as ClaimFileName,
ch.ClearinghouseName,
fm.description as FilingMethod,
'Electronic' as FilingType
into #temp

FROM EDIClaimFile ecf

INNER JOIN EDIClaim ec ON ecf.EDIClaimFileId = ec.EDIClaimFileId
INNER JOIN InsuranceCarriers ic ON ec.InsuranceCarriersId = ic.InsuranceCarriersId
INNER JOIN InsuranceCarrierCompany icc ON ic.InsuranceCarriersId = icc.InsuranceCarriersId
INNER JOIN patientvisit pv on ec.patientvisitID = pv.patientvisitID
INNER JOIN patientprofile pp on pv.patientprofileID = pp.patientprofileID
LEFT JOIN (select * from medlists where tablename= 'FilingMethods') fm on ec.filingmethodMID = fm.medlistsID
INNER JOIN clearinghouse ch on ecf.clearinghouseID = ch.clearinghouseID

WHERE ecf.FileTransmitted >= @.startdate
AND ecf.FileTransmitted < @.enddate
AND --Filter on ticket
(
(NULL IS NOT NULL AND pv.ticketnumber = @.ticketnumber) OR
(NULL IS NULL)
)
AND --Filter on company
(
(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Carrier
(
(NULL IS NOT NULL AND ec.insurancecarriersID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Provider
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Patient
(
(NULL IS NOT NULL AND pv.PatientProfileID IN (NULL)) OR
(NULL IS NULL)
)

-- Paper Claims
INSERT INTO #temp (Carriername, --CarrierAddress, CarrierCity, CarrierState, CarrierZip,
ticketnumber, dateofservice, patientname, patientID, visitchargesfiled,
visitproceduresfiled, ClaimPrinted, FirstFiledDate, /*Days,*/ filedby, claimfilename, clearinghousename,
filingmethod, FilingType)

SELECT ISNULL(pvpc.Name,'No Carrier') AS CarrierName,
pv.Ticketnumber,
pv.visit as DateOfService,
pp.last+', '+pp.first as PatientName,
pp.PatientID,
pvpc.Charges as VisitChargesFiled,
pvpc.Procedures as VisitProceduresFiled,
pv.firstFiledDate AS FirstFiledDate,
-- DATEDIFF(day,pv.Visit,pv.firstfileddate)AS Days,
pvpc.created as ClaimPrinted,
pvpc.createdby as FiledBy,
'Paper' as claimfilename,
'' as clearinghousename,
fm.description as FilingMethod,
'Paper' as FilingType

FROM PatientvisitPaperClaim pvpc

INNER JOIN patientvisit pv on pvpc.patientvisitID = pv.patientvisitID
INNER JOIN patientprofile pp on pv.patientprofileID = pp.patientprofileID
LEFT JOIN (select * from medlists where tablename= 'FilingMethods') fm on pvpc.filingmethodMID = fm.medlistsID

WHERE pvpc.created >= @.startdate
AND pvpc.created < @.enddate
AND --Filter on ticket
(
(NULL IS NOT NULL AND pv.ticketnumber = @.ticketnumber) OR
(NULL IS NULL)
)
AND --Filter on company
(
(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
-- AND --Filter on Carrier
-- (
-- (NULL IS NOT NULL AND ec.insurancecarriersID IN (NULL)) OR
-- (NULL IS NULL)
-- )
AND --Filter on Provider
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Patient
(
(NULL IS NOT NULL AND pv.PatientProfileID IN (NULL)) OR
(NULL IS NULL)
)

IF '1' = '1'
BEGIN
select *
from #temp
order by ticketnumber
END

IF '1' = '2'
BEGIN
select *
from #temp
where filingtype = 'Electronic'
order by ticketnumber
END

IF '1' = '3'
BEGIN
select *
from #temp
where filingtype = 'Paper'
order by ticketnumber
END

drop table #temp

This is the following error message I get when running in Query Analyzer:

Server: Msg 260, Level 16, State 1, Line 80
Disallowed implicit conversion from data type datetime to data type int, table 'tempdb.dbo.#temp000000002E3D', column 'DateSpan'. Use the CONVERT function to run this query.

|||

JeffS23 wrote:

-- Paper Claims
INSERT INTO #temp (Carriername, --CarrierAddress, CarrierCity, CarrierState, CarrierZip,
ticketnumber, dateofservice, patientname, patientID, visitchargesfiled,
visitproceduresfiled, ClaimPrinted, FirstFiledDate, /*Days,*/ filedby, claimfilename, clearinghousename,
filingmethod, FilingType)
SELECT ISNULL(pvpc.Name,'No Carrier') AS CarrierName,
pv.Ticketnumber,
pv.visit as DateOfService,
pp.last+', '+pp.first as PatientName,
pp.PatientID,
pvpc.Charges as VisitChargesFiled,
pvpc.Procedures as VisitProceduresFiled,
pv.firstFiledDate AS FirstFiledDate,
-- DATEDIFF(day,pv.Visit,pv.firstfileddate)AS Days,
pvpc.created as ClaimPrinted,
pvpc.createdby as FiledBy,
'Paper' as claimfilename,
'' as clearinghousename,
fm.description as FilingMethod,
'Paper' as FilingType

You have the ClaimPrinted field out of sequence so that when the Days field is uncommented the field types do not match. I think you just need to get the fields in the insert list and the select list into sequence.

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 entry for querying analysis service cube

Datetime entry for querying analysis service cube

Friday, February 17, 2012

DateDiff Issue - Need some coding Assistance

db error

Blog Archive

About Me