Can I impersonate a userid in sql server odbc connection to Sql Server 2000?

My client gave me a domain/userid which I use with terminal server and mapped network drives to do work for them. However, I'd like to set up odbc access from my client pc (which is not on their domain) to their database for testing. It seems like you only have 2 choices - trusted authentication or sql authentication. Is there any way to use trusted authentication but change the userid? This would be similar to the map network drive "connect using a different user id" function.

I'm connecting to a Win2003 server running SQL Server 2000 from a WinXP PC.

Not realy, if you are using trusted connections you need to have a valid username and password that is allowed on the sql server. Is there a way that you could get the DBA to add your workstation\username to the database, then you should be able to pass your details... The other option would be to get another user set up that you could terminal serve in with and test with that.

|||Thanks for the answer. I do use terminal server but I have a problem in that once i minimize the screen or close it and come back in, my access database that is running minimizes and won't come back. So I thought I'd use odbc from my workstation to check on the status of the programs. I'll pursue having them add my workstation and userid as a trusted userid on that server.

Can I define a query governor limit in a connection string rather then the server based op

Hi,
Can I use a query governor option in a connection string?
if yes, what is the property name I have to add in the connection string?
I don't want to setup this at the server level but only in specific
connection strings used by my users when then do ad-hoc reporting using
Report Builder.
I want to limit these users only when they create reports. (to insure that
they don'T execute bad queries which can kill the server)
other reports generated by developpers and some complex queries must always
run. and some of them are really big; if I activate the query governor of
the server, then the server always refuse to execute them. So I can't
activate the query governor at the server level to allow the developpers to
do their job.
thanks.
Jerome.Hi Jerome
I don't think that this is possible, what you may want to do is report of a
snapshot rather than any live system to reduce the impact of such problems.
John
"Jéjé" wrote:
> Hi,
> Can I use a query governor option in a connection string?
> if yes, what is the property name I have to add in the connection string?
> I don't want to setup this at the server level but only in specific
> connection strings used by my users when then do ad-hoc reporting using
> Report Builder.
> I want to limit these users only when they create reports. (to insure that
> they don'T execute bad queries which can kill the server)
> other reports generated by developpers and some complex queries must always
> run. and some of them are really big; if I activate the query governor of
> the server, then the server always refuse to execute them. So I can't
> activate the query governor at the server level to allow the developpers to
> do their job.
> thanks.
> Jerome.
>
>

Can i connect to SQLExpress instance using (local)

Hello,

I am using the following connection string to connect to SQL Server 2005 Express Edition installed on my local PC.
string sqlstr = "Server=(local);Database=MyDB;User ID=sa;Password=sa;Trusted_Connection=False";

And it gives me the following error. The string works fine in computer with SQL Server 2000 instance but fails with 2005. The string also fails for "Server = localhost".
However the string works very fine if I use the name of Server 2005 i.e ./SQLExpress

===================================

Cannot connect to (local).

===================================

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

Error Number: 2
Severity: 20
State: 0

Now the main problem is that, of course I could use a named instance, but since I am embedding the Connection String in a dll file so that any user can use it to connect it to the local instance of the SQL Server 2005 installed in his/her computer, I need to use "server = localhost". Doesn't SQL 2005 support this. If yes, where could i find option to enable it?

I have enabled every option available in surface area configuration for SQL Server 2005, but nothing seems to help out. I even use local as well as remote connections using both TCP/IP and named pipes.
Can nyone help me out?

By default SQL Server 2005 Express edition is installed as a named instance with name "SQLExpress". As a result, the connection string should specify the instance, e.g. ".\SQLExpress" or "(local)\SQLExpress" to distinguish it from a possible default instance of SQL Server that may be installed on the same machine. If you install SQL Express as a default instance then "." or "(local)" are the proper ways to specify in the connection string.|||Is there a way to convert the named instance to a default one apart from re-installation. I have only a single named instance on my computer.|||The only way to change the instance name is to re-install - uninstall and install as default instance.|||Thanks a lot, Peter. That solves it all.sql

Can connect with IP number but not with machine name

Running MSDE2000A named instance on an XP Pro machine as part of a domain
(W2003 server) , connecting from a client: with a connection
'machinename\instancename' i get no connection ('server not found or accss
denied'); however with the same setup and a connection
'172.0.30.14\instancename' everything works as expected. I can use an alias
for the IP number and that works but an alias for the machine name does not.
With a DHCP server, the IP address isn't permanent so how do I configure the
connection in my application?More info - Colleagues have suggested that this is a simple DNS problem. But
I can connect to the machine using the DNS names in other apps (e.g. 'my
network Places') - it's just SQL that won't connect.
"quilkin" wrote:
> Running MSDE2000A named instance on an XP Pro machine as part of a domain
> (W2003 server) , connecting from a client: with a connection
> 'machinename\instancename' i get no connection ('server not found or accss
> denied'); however with the same setup and a connection
> '172.0.30.14\instancename' everything works as expected. I can use an alias
> for the IP number and that works but an alias for the machine name does not.
> With a DHCP server, the IP address isn't permanent so how do I configure the
> connection in my application?

Can connect to Database, but how do I insert values into SQL Database?

I was able to connect to the SQL Database Pension with table clients with table values: ID, State, Name.
'Create(connection)
Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
'open connection
conn.Open()

However, I'm not sure how to insert a new row with an incremental ID number and a new State and Name.

Sorry, I'm really new with VWD.

You need to read about ADO.NET before you do anything.

Google it.

Can a stored procedure access another database?

Hi all,
I have an urgent problem. Can a stored procedure create a connection or access data from another database?
Thanks in advace. :)Hi all,

I have an urgent problem. Can a stored procedure create a connection or access data from another database?

Thanks in advace. :)
you know the db name? try USE db name|||Refer to the table within your SP as databasename.owner.tablename, and use it as normal. If the table sits in a different server, create a linked server and use servername.databasename.owner.table.|||I have an urgent problem. Can a stored procedure create a connection or access data from another database? Do you mean another SQL Server database, or a different database engine (like DB2 or Oracle)? Can the SQL Server "see" the other database?

-PatP|||Hi all,

I have an urgent problem. Can a stored procedure create a connection or access data from another database?

Thanks in advace. :)
What is the problem?|||Ermz..

The thing I want to do is this, I have a local database and I need to write a local stored procedure that is able to retrieve data from the local tables AND data from tables that are in another remote SQL server.

So from the threads I read, I just connect the remote server with my local one in the enterprise manager and use remoteserver.database.owner.table to get the data right?

Thanks a lot!!! :)

Victoria|||errrr, not exactly. You need to setup a linked server first or use OPENROWSET. Search the forum for linked server and you'll get plenty of examples. If you setup the linked server, you then use your syntax. If not, you will need to use OPENROWSET.|||I guess by saying "...in Enterprise Manager..." she meant to set up a linked server there. In short, - yup, you got it ;)|||Hi,

think I'll be using the linked server then cos my query to another database will go on every few mins. So for that's should be it until i really test it out. If my local database server is oracle 9i then is the concept somehow simliar, meaning that i have to be connected to the remote sql server right? If so, how do I do it? Cos there's no linked server for me to create in the oracle enterprise manager.

Thanks a lot for all of your help!!! :)|||From Horacle to SQL you need a DBLink, - very similar concept, but ironically is even easier to set up. Use the Horacle's EM, it's straight forward.|||Hi,

I'll be looking into the DBLink for oracle a while later. Thanks for the advice.

For MS Sql server,
I added the remote server to my local server as a linked server successful. I also set the ANSI_NULLS using the sp_configure procedure. But when i write codes to access the remote server in a stored procedure, the EM keep telling me that i have to set the ANSI_NULLS and ANSI_WARNINGS. I tried the same codes in the query analyser and it has no problem.

Can someone help? Thanks!!! :)|||Hi, i have sloved the problem that is to create the stored procedure using query analyser. Somehow the enterprise manager is a bit "something wrong". =)

For the people who want to know how to create a DBLink in oracle, here is a very good tutorial.

http://www.oracle-base.com/articles/9i/HSGenericConnectivity9i.php

Can a Connection Manager be parameterized?

Can you create one connection manager that accepts the Server name and database name at run time and constantly reuses the server and datbase name at runtime when executing one SSIS solution with Multiple packages.

You can use Expressions property in your connection manager to build the connection string at run time. You might also want to use package configurations to 'pick up' the right values from a configuration file/table.

Jamie has something about it on his blog,

http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

Rafael Salas