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 carry my existing DB users to a new server and restore??

Hi,

Quick question, I have about 20 users in my local server and database. We
are looking to restore that database to a new server in a new network and
still be able to retain the database users.

We have created all 20 logins in the new server (exact names) but when we
restore the database I do not see the users anymore!

Is there something we can do to preserve them or a script we can run to link
them again?

Thanks a bunch!!!

S"SD" <npspam@.nowhere.net> wrote in message
news:138249382eb68f5a6766a476adc3da38@.news.teranew s.com...
> Hi,
> Quick question, I have about 20 users in my local server and database. We
> are looking to restore that database to a new server in a new network and
> still be able to retain the database users.
> We have created all 20 logins in the new server (exact names) but when we
> restore the database I do not see the users anymore!

sp_change_users_login is probably what you need.

> Is there something we can do to preserve them or a script we can run to
link
> them again?
> Thanks a bunch!!!
> S
>|||"Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in message news:<7Jtjc.122289$e17.75072@.twister.nyroc.rr.com>...
> "SD" <npspam@.nowhere.net> wrote in message
> news:138249382eb68f5a6766a476adc3da38@.news.teranew s.com...
> > Hi,
> > Quick question, I have about 20 users in my local server and database. We
> > are looking to restore that database to a new server in a new network and
> > still be able to retain the database users.
> > We have created all 20 logins in the new server (exact names) but when we
> > restore the database I do not see the users anymore!
> sp_change_users_login is probably what you need.
>
> > Is there something we can do to preserve them or a script we can run to
> link
> > them again?
> > Thanks a bunch!!!
> > S

You can run the following script to generate a create script for all
of your users. It will generate a create statement for each login
using the encrypted password and SID. Replace "MY_DB" in the script
with your database name, then run the script against your source
database. Copy the output of the script to your destination server and
run it. Then, when you restore your database, your users will
automatically be synched with their logins. Hope this helps!

Lisa

-----
--Description: Scripts logins for one db
--*****IMPORTANT replace MY_DB with the name of the database that
holds the users that you need scripted

-- Begin Script, Create sp_help_revlogin procedure --
USE master
GO
SET NOCOUNT ON
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_MY_DBLogins @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)

IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR

SELECT a.sid, a.name, a.xstatus, a.password FROM
master..sysxlogins a
join MY_DB..sysusers MY_DB on a.sid = MY_DB.sid
WHERE a.srvid IS NULL AND a.name <> 'sa'

ELSE
DECLARE login_curs CURSOR FOR
SELECT a.sid, a.name, a.xstatus, a.password FROM
master..sysxlogins a
join MY_DB..sysusers MY_DB on a.sid = MY_DB.sid
WHERE a.srvid IS NULL AND a.name <> 'sa'

OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus,
@.binpwd
IF (@.@.fetch_status = -1)
BEGIN
--PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd
+ ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' +
@.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus,
@.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
-- End Script --
--
EXEC master..sp_help_MY_DBLogins
--drop proc sp_help_MY_DBLogins

Can I access SQL Server 2005 via a Window XP pro workgroup network?

Hi,

I am new to SQL Server and I am trying to install SQL Server 2005 Express or Stnadard Edition on a computer runing XP pro on a XP's workgroup network using a router. Can I access the the database from another computer running XP pro on the same network? If I can, what setting do I need when installing SQL 2005 in order to do this?

Any help is appricated.

Make only sure that you enable remote connections, see the Screencast for this on my site for a detailed explanation.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Can I access sql 2000 from machine having sql2005

Dear All,

I am developing a network application in asp.net. The database is in local machine having sql 2005. But the user database is already existent. So I am accessing that database which is sql2000. does it give any problem while connecting from sql2005 machine to sql2000. If yes it is giving me error as follows:

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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)

connection string is :

"Data Source=192.168.1.16,1433;Network Library=DBMSSOCN;Initial Catalog=mycatalog;User ID=myuserid;Password=mypwd;"

Please correct me if I am wrong.

Thanks and Regards.

Fazal

This indicates that your TCP/IP was not enabled or your sql server was not listening on the appropriate port.

|||

Go to "Microsoft SQL Server 2005" in programm files => onfiguration Tools =>SQL Server Surface Area Configuration =>first option=>Remote Connections

and here allow remote connection = Use both TCP/IP and name pipes => OK

After that you must restart SQL Server 2005 in order to apply the modification and try againa

Popa IUlia

_____________________

MCP.MCAD.MCSD

Can a SQL Server Express talk with other SQL Server Express in the same network?

Hi

There is a service from SQL Server 2005 that "listens" to the network, if the network is down, all data gets saved within the local instance of the SQL server 2005. And when the network becomes available, the local instance of the sql server will automatically "feed" the local records to the central SQL server 2005.

What is that feature called?

It sounds like you're talking about Service Broker, but Service Broker doesn't support communication between two Express instances. Service Broker communication between SQL instances must include a paid edition somewhere in the message chain. I'm not aware of any other tools that support the type of communication you've describe.

Mike

|||

Mike,thanks for the information.

I will look into service broker

Thanks

Can 98SE read files from NTFS Drives?

I have been told by a local PC club technician that 98SE cannot read NTFS
drives in a network. Is this true? TIA, Jim.http://groups.google.co.uk/groups?h...news.dfncis.de

Simon|||"Jim Richards" wrote:

> I have been told by a local PC club technician that 98SE cannot read
> NTFS drives in a network. Is this true? TIA, Jim.

In addition to the excellent thread linked to in another message, please
allow me to clarify the situation.

Forgive me if you know all this, but perhaps someone else reading this
thread in the future won't. Also, lest this be seen as OT, I think there
are plenty of people that end up in situations managing SQL Server even
though they've never been trained on things like hardware and OS
operations... So, to make a long story, er, less long:

All (modern) OS's use layers of abstraction. A Windows computer has a
driver to talk to the physical disk drive via the bus it's connected to
(SCSI, ATA/IDE, SATA, etc). A file system driver sits "on top" of that to
organize the disk into a logical view. Without the file system driver, you
have to access the disk by "block": I've seen mainframe programs based
around this and *trust* me, it's ugly :) FAT32, NTFS, ext3, ReiserFS, et al
are file systems that allow you to view a physical disk as directories and
files. Without them, you couldn't load the system library
c:\windows\system32\user32.dll... You'd need to know to read blocks 345-392
to get that DLL (and that's vastly oversimplified).

This often confuses people because Windows allows you to map a drive to a
network share. However, that mapped drive is *not* sending commands
directly to the disk device on the server. The mapped drive is presented by
a driver that talks to the server over a network instead of directly to a
physical drive. Not only is this far better, but it's really the only
option... Can you imagine 100 client computers all trying to physically move
the drive heads around on your server?

Another way to think of it is that network file servers work like SQL
Server: you send a request and you get a response. You don't really care if
the server read your file from a single IDE drive formatted with FAT32, a
7TB RAID 1+0 disk array formatted with NTFS 5, or from a special interface
to thousands of trained orangutans with legal pads (which is how we run our
servers at my company ;).

The problem with Win9x is that there's no NTFS file system driver available
from Microsoft (although it wouldn't surprise me if someone on the Internet
had hacked one together). So Win9x can't talk to a physical drive formatted
with NTFS. But this doesn't matter on the network because the server is in
charge of the NTFS drive.

So, the PC tech is completely wrong. But...

There's a protocol known as iSCSI that allows you send SCSI bus commands
over TCP/IP. It allows you to use a network as the bus to a drive array.
If for some bizarre reason you managed to get a hold of an iSCSI driver for
a Windows 98 computer so that you could access a networked drive array,
you'd need a FAT16 or FAT32 partition on the array since the Win98 computer
would be sending physical commands to the array. But... that's mostly
theoretical: I can't imagine that happening unless your were a masochist,
had tons of cash, and then got drunk and decided to hire some systems
programmers to put together incredibly bizarre systems :)

Craig

Can 2005 and 2000 have use same shared network drive as resource

Can 2005 and 2000 have use same shared network drive as resource?
Example: If 2000 uses I,J,K Network drives can 2005 use I:\2005,j:\2005 and
k:\2005 .
We have A/A cluster, Can i upgrade one instance to 2005 and keep another as
2000 for some time.
Thanks
IT
"IT" <IT@.discussions.microsoft.com> wrote in message
news:2B430FAC-5A0E-4F65-B28F-6C51D1AD31EA@.microsoft.com...
> Can 2005 and 2000 have use same shared network drive as resource?
Not if they are configured correctly. Since each instance will have its own
cluster group and thus its own virtual server, then the resources from one
cluster group can not be used by another cluster group at the same time.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
Denver starting Feb 12th
NYC starting Feb 19th

> Example: If 2000 uses I,J,K Network drives can 2005 use I:\2005,j:\2005
> and
> k:\2005 .
> We have A/A cluster, Can i upgrade one instance to 2005 and keep another
> as
> 2000 for some time.
> Thanks
> IT
>
|||Thank you for the reply. I have one more question on A/A cluster(right now
its sql 2000 A/A cluster), Can i upgrade one instance to 2005 and keep
another as 2000 for some time.
Thanks in advance.
"Russ Kaufmann" wrote:

> "IT" <IT@.discussions.microsoft.com> wrote in message
> news:2B430FAC-5A0E-4F65-B28F-6C51D1AD31EA@.microsoft.com...
> Not if they are configured correctly. Since each instance will have its own
> cluster group and thus its own virtual server, then the resources from one
> cluster group can not be used by another cluster group at the same time.
>
> --
> Russ Kaufmann
> MVP - Windows Server - Clustering
> ClusterHelp.com, a Microsoft Certified Gold Partner
> Web http://www.clusterhelp.com
> Blog http://msmvps.com/clusterhelp
> The next ClusterHelp classes are:
> Denver starting Feb 12th
> NYC starting Feb 19th
>
>
|||"IT" <IT@.discussions.microsoft.com> wrote in message
news:60AC40F5-FB03-4265-9BDF-2174C196DF81@.microsoft.com...
> Thank you for the reply. I have one more question on A/A cluster(right now
> its sql 2000 A/A cluster), Can i upgrade one instance to 2005 and keep
> another as 2000 for some time.
Sure. You can have different versions of SQL running in different instances.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
Denver starting Feb 12th
NYC starting Feb 19th

Calling the New server Registration Dialog window

Hi,

I'm aware of using SmoApplication.EnumAvailableSqlServers to list network SQL Servers, however I would like to go a step-further and a call the "New Registered Server" dialog (follow the below link), in a similar way that you can use an instance of DataLinksClass to call a udl style connection string builder.

Is this an object that can this be called, or will I need to build it?

http://www.thesalemlot.com/serverreg.jpg

Thanks

Drew

AS SQL Server MS is just a WIndows application built on top of SMO you can′t call this functio, you have to build that on your own. Bu you can take the one Microsoft has built as a template. Simply refelctor the code and take this a a good starting point to built your own register diallog.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Well, I'd never heard off Reflector until you mentioned it. But if I understand you correctly, this could really help me for this and future projects. I've download it, but am unsure how to use it. I'd appreciate it if you could spare a moment to explain it. How do I load that particular part of SMO to view the code behind it?

Thanks

Drew

|||Well you have to reflector the code of SSMS to find the appropiate parts. Find the Forms section within the assembly and see what is happening behind the scenes-

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||THANKS FOR REGISTERED MY NEW WINDOW

Calling the New server Registration Dialog window

Hi,

I'm aware of using SmoApplication.EnumAvailableSqlServers to list network SQL Servers, however I would like to go a step-further and a call the "New Registered Server" dialog (follow the below link), in a similar way that you can use an instance of DataLinksClass to call a udl style connection string builder.

Is this an object that can this be called, or will I need to build it?

http://www.thesalemlot.com/serverreg.jpg

Thanks

Drew

AS SQL Server MS is just a WIndows application built on top of SMO you can′t call this functio, you have to build that on your own. Bu you can take the one Microsoft has built as a template. Simply refelctor the code and take this a a good starting point to built your own register diallog.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Well, I'd never heard off Reflector until you mentioned it. But if I understand you correctly, this could really help me for this and future projects. I've download it, but am unsure how to use it. I'd appreciate it if you could spare a moment to explain it. How do I load that particular part of SMO to view the code behind it?

Thanks

Drew

|||Well you have to reflector the code of SSMS to find the appropiate parts. Find the Forms section within the assembly and see what is happening behind the scenes-

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||THANKS FOR REGISTERED MY NEW WINDOW