Distributed Query Connection Issues

I have the following situation: I have a database on ServerA and a database
on ServerB. My client app connects to ServerA and uses a stored procedure to
retrieve a dataset from both ServerA and ServerB.
While developing the app (using SQL 2K Dev on my workstation), I used a
linked server approach. I linked ServerB and refered to the database as
ServerB.DBName.dbo.TableName and it worked fine.
I'd like to use trusted connections and my users will have SQL server access
to both servers.
Here is the problem:
If I install the app on another workstation (i.e. not on the one running
ServerA), the I get:
Msg 18452
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Using Query Analyser from the third workstation I can connect to both
serverA and ServerB using Windows Authentication, with both Named Pipes and
TCP/IP protocols.
What am I doing wrong?
Thank you very much for your help.
The issue you are hitting is sometimes called a double hop.
You are wanting to pass Windows credentials from one server
to another. The following KB article explains the issue and
still applies to SQL Server 2000 if you don't have kerberos
enabled and account delegation setup. The article specifies
SQL 7 as you can't get around this on SQL 7 and have to use
other methods to not hit issues with double hops.
PRB: Message 18456 from a Distributed Query
http://support.microsoft.com/?id=238477
Under SQL Server 2000, If you want to use Windows
authentication in this scenario, you need to use Active
Directory, enable kerberos and setup account delegation.
You can find more information in SQL Server books online
under the topic Security Account Delegation
-Sue
On Mon, 10 Jan 2005 10:55:01 -0800, "Konstantin"
<Konstantin@.discussions.microsoft.com> wrote:

>I have the following situation: I have a database on ServerA and a database
>on ServerB. My client app connects to ServerA and uses a stored procedure to
>retrieve a dataset from both ServerA and ServerB.
>While developing the app (using SQL 2K Dev on my workstation), I used a
>linked server approach. I linked ServerB and refered to the database as
>ServerB.DBName.dbo.TableName and it worked fine.
>I'd like to use trusted connections and my users will have SQL server access
>to both servers.
>Here is the problem:
>If I install the app on another workstation (i.e. not on the one running
>ServerA), the I get:
>Msg 18452
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Using Query Analyser from the third workstation I can connect to both
>serverA and ServerB using Windows Authentication, with both Named Pipes and
>TCP/IP protocols.
>What am I doing wrong?
>Thank you very much for your help.

Distributed Query Connection Issues

I have the following situation: I have a database on ServerA and a database
on ServerB. My client app connects to ServerA and uses a stored procedure to
retrieve a dataset from both ServerA and ServerB.
While developing the app (using SQL 2K Dev on my workstation), I used a
linked server approach. I linked ServerB and refered to the database as
ServerB.DBName.dbo.TableName and it worked fine.
I'd like to use trusted connections and my users will have SQL server access
to both servers.
Here is the problem:
If I install the app on another workstation (i.e. not on the one running
ServerA), the I get:
Msg 18452
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Using Query Analyser from the third workstation I can connect to both
serverA and ServerB using Windows Authentication, with both Named Pipes and
TCP/IP protocols.
What am I doing wrong?
Thank you very much for your help.The issue you are hitting is sometimes called a double hop.
You are wanting to pass Windows credentials from one server
to another. The following KB article explains the issue and
still applies to SQL Server 2000 if you don't have kerberos
enabled and account delegation setup. The article specifies
SQL 7 as you can't get around this on SQL 7 and have to use
other methods to not hit issues with double hops.
PRB: Message 18456 from a Distributed Query
http://support.microsoft.com/?id=238477
Under SQL Server 2000, If you want to use Windows
authentication in this scenario, you need to use Active
Directory, enable kerberos and setup account delegation.
You can find more information in SQL Server books online
under the topic Security Account Delegation
-Sue
On Mon, 10 Jan 2005 10:55:01 -0800, "Konstantin"
<Konstantin@.discussions.microsoft.com> wrote:

>I have the following situation: I have a database on ServerA and a database
>on ServerB. My client app connects to ServerA and uses a stored procedure t
o
>retrieve a dataset from both ServerA and ServerB.
>While developing the app (using SQL 2K Dev on my workstation), I used a
>linked server approach. I linked ServerB and refered to the database as
>ServerB.DBName.dbo.TableName and it worked fine.
>I'd like to use trusted connections and my users will have SQL server acces
s
>to both servers.
>Here is the problem:
>If I install the app on another workstation (i.e. not on the one running
>ServerA), the I get:
>Msg 18452
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Using Query Analyser from the third workstation I can connect to both
>serverA and ServerB using Windows Authentication, with both Named Pipes and
>TCP/IP protocols.
>What am I doing wrong?
>Thank you very much for your help.

Display of date time inforamtion - some columns are NULL some are

Do the formatting where it belongs - on the client. In SQL you could use
CONVERT but then you loose the domain.
Another way would be to use sql_variant which retains the datatype for later
use, but that's just another complication that can be successfully avoided b
y
formatting the data on the client.
ML
http://milambda.blogspot.com/BCP doesn't have any formatting control logic built in.
Before making definitive statements you need to know more about what the
poster is trying to achieve.
I've done formatting lots of time in the SQL Server because thats been the
best place for that particular problem, for instance creating the output
file for a data feed, its very easy (a couple of lines of T-SQL) to create a
table and use BCP to output it to a file that can then be sent out to
another part of the company/supplier.
A statement like 'do the formatting where it belongs - on the client' is
meaningless in this instance.
Its more true to say 'Do the formatting where it makes sense and is most
efficient for the problem you are trying to solve'.
Sorry if the post sounds harsh, I'm a bit fed up with this do all formatting
on the client debarkle.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"ML" <ML@.discussions.microsoft.com> wrote in message
news:A73D446B-49C0-429A-B30C-EB11A035B5E7@.microsoft.com...
> Do the formatting where it belongs - on the client. In SQL you could use
> CONVERT but then you loose the domain.
> Another way would be to use sql_variant which retains the datatype for
> later
> use, but that's just another complication that can be successfully avoided
> by
> formatting the data on the client.
>
> ML
> --
> http://milambda.blogspot.com/

Display list of databases in App_Data folder

Hi All,

The web site I'm working on will allow admin users to create a new database for each new client. I'm hoping to be able to keep all of the databases in the local App_Data folder, but I'm finding that I can't even find a way of generating a list of the databases in the App_Data folder.

The code I have is:

Dim serverConnAs New ServerConnection(".\SQLEXPRESS")serverConn.LoginSecure =TrueserverConn.ConnectTimeout = 30serverConn.Connect()Dim sqlServerAs Server =New Server(serverConn)For Each dbAs DatabaseIn sqlServer.Databases ListView1.Items.Add(db.Name)Next

This does populate a list of databases, but not the ones in the App_Data folder - just those attached directly to the server.

Any help on this will be greatly appreciated as it's starting to drive me nuts!

Thanks,

Paul

Hi Paul,

What you are getting is correct. You will get a list of databases running on the server.

If you want to create databases on a particular directory you have to override the default directory

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data or something similar depending on where is your default data directory.

You have to specify that in your create database script

USE MASTER

CREATE DATABASE DataBaseName
ON
(
NAME = LogicalName_Data,
FILENAME = 'c:\YourPath\App_Data\DataBaseName_Data.mdf',
SIZE = 4,
MAXSIZE = 10,
FILEGROWTH = 1
)
LOG ON
(
NAME = 'LogicalName_Log',
FILENAME = 'c:\YourPath\App_Data\DataBaseName_Log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)

Use

Server server = new Server(new ServerConnection(cnnDb));
server.ConnectionContext.ExecuteNonQuery(sqlString);

to create the new database.

If want to find all databases that are present in the directory.

You have to do a naming convention trick of naming the database starting with

GeneratedDb_ and filter the names based on it or

You can use DirectoryInfo class and read the App_Data folder and get all files having extension .mdf

Or loop through the filegroups in Database and check for App_Data folder

Happy programming,
Anton

|||

Hello,

this may be helpful to u

http://www.codeproject.com/aspnet/dbadmin.asp

http://www.codeproject.com/aspnet/MyDbViewerSite.asp

|||

Hi guys,

Just wanted to say a big thank you for your replies. It looks like this is tougher than I expected as I don't think I can get away with updating the master database to force the creation of the databases (ultimately, the web app won't be loaded on my server), and I had loved the idea of using '|DataDirectory|' so that I didn't need to explicitly give the server name for the connection strings already created. However, I think I've read enough now to believe that I am better off setting things to work directly with the server rather than locally - at least I seem to be able to create new databases and copy data as I need to.

Thanks again,

Paul

Display IP address from client in a report

I know that may seem like a weird request, but bear with me. I have multiple possible web sites that would request a report from a given Report Server, and I'd like the IP address of whatever machine requested the report to be displayed somewhere on said report. Is there a way to achieve this?

Thank you!

NOTE: To simplify things, I literally want to display whatever IP the reporting server sees. No need to think complicated about if its a server, a client, etc. Just whatever IP the report server is seeing.

You can use a parameter in query string that contain IP address of the server macchine that require the report.

|||

You can get a client's IP address by:

string GetIPAddress(){return HttpContext.Current.Request.UserHostAddress;}

You can put this code in a custom assembly. You can call this assembly in your report:http://geekswithblogs.net/davyknuysen/archive/2007/03/26/109901.aspx

|||

I thought about embedding code, but unfortunately, the Request object is not set in a report (had to import System.web in it first to test that).

And sending the IP as a parameter would be the easy way, with a catch: Servers tend to have a lot of IPs, even more so if you include the IPv6 ones. Its difficult to "guess" which one is exposed to the server. Can do some tricks using subnet masks and such, but the ideal would be for the server to find it, itself.

My plan B is to have a config in machine.config or something (multiple apps need it, not just web) with the IP as a key, and we'll have to make sure not to forget to change it, or something...