Connect to SQL Server: February 2012

Saturday, February 25, 2012

Connecting through VPN fails

Hi all!
At work, I've no problems connecting to our testserver. From home I have
problems. I use VPN to get inside our firewall, and everything else works as
expected. Whether I try to connect to my testdata from my application, or I
try to connect using SQL Query Analyzer, I get the same errormessage (pasted
in here):
Unable to connect to server OSS24DBTEST:
ODBC: Msg 0, Level 16, State 1
[Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context
The servers is set up to use both Sql server and Windows authentication.
Anyone that have a clue where to start?
I'm a newbe using SQL server 2000
Thanks
ATWhen you use VPN, you log onto the domain from home, right? Otherwise, it
won't work.
When I use VPN, I often Remote Desktop into a machine at the office, and log
onto the domain there.
Jeff
"A.Taurus" <nospam@.spam.no> wrote in message
news:cl6d9f$slu$1@.services.kq.no...
> Hi all!
> At work, I've no problems connecting to our testserver. From home I have
> problems. I use VPN to get inside our firewall, and everything else works
as
> expected. Whether I try to connect to my testdata from my application, or
I
> try to connect using SQL Query Analyzer, I get the same errormessage
(pasted
> in here):
> Unable to connect to server OSS24DBTEST:
> ODBC: Msg 0, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context
> The servers is set up to use both Sql server and Windows authentication.
> Anyone that have a clue where to start?
> I'm a newbe using SQL server 2000
> Thanks
> AT
>
>

Connecting through VPN fails

Hi all!
At work, I've no problems connecting to our testserver. From home I have
problems. I use VPN to get inside our firewall, and everything else works as
expected. Whether I try to connect to my testdata from my application, or I
try to connect using SQL Query Analyzer, I get the same errormessage (pasted
in here):
Unable to connect to server OSS24DBTEST:
ODBC: Msg 0, Level 16, State 1
[Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context
The servers is set up to use both Sql server and Windows authentication.
Anyone that have a clue where to start?
I'm a newbe using SQL server 2000
Thanks
ATHi,
Hope you have solved it in the meantime.
I do the same with a Checkpoint VPN connection over ADSL and this works well
.
Will a straight (no VPN) connection work? (Use port 1433/1434). Be sure to
have this port open.
If this works, try to see if there is a portblocking vehickle at the other
side in the VPN router, if this is OK you really have a problem.
Besides, I was pleasantly surprised with the speed of the results working
over VPN.
I saw no degradation in performance with the direct connection.
Good luck,
"A.Taurus" wrote:

> Hi all!
> At work, I've no problems connecting to our testserver. From home I have
> problems. I use VPN to get inside our firewall, and everything else works
as
> expected. Whether I try to connect to my testdata from my application, or
I
> try to connect using SQL Query Analyzer, I get the same errormessage (past
ed
> in here):
> Unable to connect to server OSS24DBTEST:
> ODBC: Msg 0, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context
> The servers is set up to use both Sql server and Windows authentication.
> Anyone that have a clue where to start?
> I'm a newbe using SQL server 2000
> Thanks
> AT
>
>

Connecting through proxy

Hi,
We are having problem with Linked Server connection through proxy in our
network. The port 1433 is open. Everything was fine before using proxy. Is
there any other configuration required?
Any help would be greatly appreciated,
Leila
Maybe: http://support.microsoft.com/kb/216415
Also, if you are using a dynamic port for SQL-Server or if you have more
than one instance of sql-server, the port 1434 and some of the following
ports must also be opened.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Leila" <Leilas@.hotpop.com> wrote in message
news:u0$T%231YlHHA.960@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We are having problem with Linked Server connection through proxy in our
> network. The port 1433 is open. Everything was fine before using proxy. Is
> there any other configuration required?
> Any help would be greatly appreciated,
> Leila
>

Connecting through proxy

Hi,
We are having problem with Linked Server connection through proxy in our
network. The port 1433 is open. Everything was fine before using proxy. Is
there any other configuration required?
Any help would be greatly appreciated,
LeilaSee:
INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/?id=287932
--
Cheers,
Stefan Delmarco
http://www.fotia.co.uk|||Maybe: http://support.microsoft.com/kb/216415
Also, if you are using a dynamic port for SQL-Server or if you have more
than one instance of sql-server, the port 1434 and some of the following
ports must also be opened.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Leila" <Leilas@.hotpop.com> wrote in message
news:u0$T%231YlHHA.960@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We are having problem with Linked Server connection through proxy in our
> network. The port 1433 is open. Everything was fine before using proxy. Is
> there any other configuration required?
> Any help would be greatly appreciated,
> Leila
>

Connecting through ODBC

Did anyone try fetching data from any database which is connected through ODBC. If so can you please share the details. In the data flow task of SSIS there is a facility for only oledb and not thru ODBC.

From what I understand, ODBC was not implemented by MS in SSIS.

See thread here

|||An ODBC Destination was not implemented due to time constraints, but you can certainly read from a database via ODBC.
You need to use the ODBC Provider which is a member of the ADO.Net connection types. You then need to use the DataReader source to query your database.
Larry Pope
|||ODBC works as a source but not a destination.

Connecting through Enterprise Manager

Hi
I am running Win2k Server on an XP machine. I have two
databases which I can run programs on with no problem. I
can connect and run queries through Query Analyzer with
no problem, but when i open Enterprise Manager to access
the Databases, the DB service stops and then I get the
message "The Connection to SQL Server has been broken.
The connection to the SQL Server is broken. Do you want
to try reconnecting to it?" I answer yes, and the prompt
for reconnecting keeps coming up. I can't access any of
my DB's through Enterprise manager. When I try to attach
a DB through the All Tasks option I get "Error 22528 [SQL-
DMO]This server connection has been unexpectedly broken
and cannot be reconnected."
Any ideas on what I need to do to rectify this problem?
Thanks
Dave LugoCheck your SQL errorlog to see if you have any stack dumps.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Connecting through Enterprise Manager

Hi
I am running Win2k Server on an XP machine. I have two
databases which I can run programs on with no problem. I
can connect and run queries through Query Analyzer with
no problem, but when i open Enterprise Manager to access
the Databases, the DB service stops and then I get the
message "The Connection to SQL Server has been broken.
The connection to the SQL Server is broken. Do you want
to try reconnecting to it?" I answer yes, and the prompt
for reconnecting keeps coming up. I can't access any of
my DB's through Enterprise manager. When I try to attach
a DB through the All Tasks option I get "Error 22528 [SQL-
DMO]This server connection has been unexpectedly broken
and cannot be reconnected."
Any ideas on what I need to do to rectify this problem?
Thanks
Dave Lugo
Check your SQL errorlog to see if you have any stack dumps.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

connecting through C#

When I look at the connection string it's:

Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ProcBuilder.mdf;Integrated Security=True;User Instance=True

This works fine on my machine, but when I try to run it on another computer it tells me:

"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."

It should not be trying to connect remotely, it's a local DB. Do I

have to install SQL Express on the machine I'm installing this on for

it to work?

hi,

data is not local to the client machine but to the machine providing the service, the machine with SQL Server (SQLExpress) installed.. and, of course, the relative database files have to be stored on that pc...

so, if you have to connect to a remote SQLExpress instance, you should modify the connection string accordingly to the "remote" SQLExpress instance..
if .\SQExpress works for a local connection, a remote one should "point" to the computer name, thus ComputerName\SQLExpress, and this is a "general" thumb for SQL Server connections...

but you are also dealing User Instance facilities, and http://msdn2.microsoft.com/en-us/library/bb264564.aspx#sqlexpuser_topic8 points out only "local connections" are supported.. you can overcome this hacking the pipe the service is listening on, but you should go for a more "traditional" use of a SQL Server engine..

or, you just install SQLExpress (and your user database(s)) on every each machine and continue using User Instance feature "locally"..

regards

Connecting the ASP.Net to a DB using SQL Query

I have a SQL query that I think it wont work it's:

SELECT break_time, break_rep_no, break_count, break_update_count, break_type, break_group
FROM breaks
WHERE (break_date = @.p1) AND (break_group = @.p2) AND (break_time > @.p3) OR
(break_time < @.P4)
ORDER BY break_time

When I insert this parameters:

@.p1= 7/21/2004
@.p2= 0
@.p3= 9:00 AM
@.p4= 3:00 PM

I get these results:
Break_time rep_no count ucount break_type break_group
9:00:00 AM____2____2_____0________2_________0
9:00:00 AM____2____2_____1________1_________0
10:00:00 AM___1____2_____0________2_________2
11:20:00 AM___2____1_____0________2_________2
1:00:00 PM____2____2_____1________1_________0

Which if you notice not the result I expect look at the (break_group) Column you will find that it brings 0's in addition to 2 which is not expected to appears?

I'm using this query in ASP.Net page Under VB.Net

Me.SqlComm_Chk_Group.Parameters(0).Value = System.DateTime.Today
Me.SqlComm_Chk_Group.Parameters(1).Value = 0
Me.SqlComm_Chk_Group.Parameters(2).Value = tFrom.AddMinutes(59).ToShortTimeString()
Me.SqlComm_Chk_Group.Parameters(3).Value = tTo.AddMinutes(-59).ToShortTimeString()

dr = Me.SqlComm_Chk_Group.ExecuteReader

While dr.Read
tBreakTime = dr(0)
iRepNo = dr(1)
iBreakCount = dr(2)
iBreakUCount = dr(3)
iBreakType = dr(4)
iBreakGreoup = dr(5)

Call Engine(tBreakTime, tFrom, iBreakType, False, iRepNo)
End While
dr.Close()

Where is the problem PLAES?
Thank youParenthesize.


SELECT break_time, break_rep_no, break_count, break_update_count, break_type, break_group
FROM breaks
WHERE (break_date = @.p1) AND (break_group = @.p2) AND ((break_time > @.p3) OR
(break_time < @.P4))
ORDER BY break_time

Even now, I am not sure the code will do what you are looking to do.|||Why I did it before and it works I even try it with OleDB with MSAccess DB But I'm not understand why its not working here every thing seems to be correct|||Depending upon the data in the db and the times you pass, it might APPEAR to work for some data.

Connecting tables from a different database to an application

I have an application using an Access 2003 project connecting through ADO
which must refer to a table in a SQLServer 2000 database other than the main
database. When I connect, I get the data but the recordset is not updatable.
I can create a view in the main SQLServer database that makes the connection
and can be updated, but when I open that view in Access, it's still not
updatable.
Is there some simple way to update the data?
Hi,
http://support.microsoft.com/kb/328828
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
"TLD" <TLD@.discussions.microsoft.com> wrote in message
news:D6D862E7-D3AB-48EA-92D2-2479620DC822@.microsoft.com...
>I have an application using an Access 2003 project connecting through ADO
> which must refer to a table in a SQLServer 2000 database other than the
> main
> database. When I connect, I get the data but the recordset is not
> updatable.
> I can create a view in the main SQLServer database that makes the
> connection
> and can be updated, but when I open that view in Access, it's still not
> updatable.
> Is there some simple way to update the data?
>
|||Thanks for your help, but I don't see the answer to my problem in the article
referenced.
"Jens K. Suessmeyer" wrote:

> Hi,
> http://support.microsoft.com/kb/328828
>
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "TLD" <TLD@.discussions.microsoft.com> wrote in message
> news:D6D862E7-D3AB-48EA-92D2-2479620DC822@.microsoft.com...
>
|||The problem turned out to be that the "uneditiable" fields were char rather
than varchar. When the user put a cursor into the field, he wasn't able to
insert letters. Changing the configuration of the underlying table has
resolved the problem.
Thanks for your help.
"Jens K. Suessmeyer" wrote:

> Hi,
> http://support.microsoft.com/kb/328828
>
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "TLD" <TLD@.discussions.microsoft.com> wrote in message
> news:D6D862E7-D3AB-48EA-92D2-2479620DC822@.microsoft.com...
>

Connecting Tables

Hey everyone,
I am beggining in SQL and the .NET framework and have been running into some problems trying to design a relational database. I am completely new to it so I bought a book that was recommended in this Forum called "Handbook of Relational Database Design" and it has been pretty usefull so far. RIght now I am trying to make the Logical Data Model before I make the Relational Data Model.
The problem that I am having right now is creating a table that is a derivation from another table. For example, in the book they have a table called Property, and then two other tables called MountainProperty and BeachProperty. MountainProperty and BeachProperty are a type (relationship here) of a property. So basically Property will hold some data in a table, and then MountainProperty and BeachProperty will extend that property to contain more specific data. This is very similar to what I want to do. However I am having a problem understanding how an instance (or row) in Property, will have a link (foreign key) to a piece of data that is in Mountain or BeachProperty. I understand the foreign key in Mountain and BeachProperty and how they will link back to their "parent". But how will Property know its children, where is the link for that, how can one make a link to that. You could make a column with a foreign key, but which table would it point to, can one column point to mulitple tables? That doesn't make very much sense to me.
Basically what I am trying to say is that a row in the Property table can be multiple types, and these types will store more additional data about that row. How can I link to that data from that row in the table Property.
The only way that I can think of to link to that table is by making another column called PropertyType where MountainProperty equal 1 and BeachProperty equals 2. So I would perfrom a query in my application on the row like "Select PropertyType from Property" which would give me back 1 or 2. Based upon that I would then know which table to search for in the database and would then perfrom another query on the specific table, either MountainProperty or BeachProperty.
I am terribly sorry if this is confusing or if it is so appartently easy for you, but this is the first time that I have ever tried to make a relational database and I am really struggling on seeing how to organize these tables properly. Thank yor for your time.
Jeremy

(But how will Property know its children, where is the link for that, how can one make a link to that. You could make a column with a foreign key, but which table would it point to, can one column point to mulitple tables? That doesn't make very much sense to me.
Basically what I am trying to say is that a row in the Property table can be multiple types, and these types will store more additional data about that row.)

Hi,

I tried the answer your question but could not find it, tables in the relational model since I have been doing data back in 1998 are entities, the only property per ANSI SQL definition in the DDL(data definition language) is IDENTITY. So you design tables by files and association and relationship is determined by upper and lower bound cardinality.

There is a better book called Data Modeling by G. Lawrence Sanders and there are existing free data models you can clone to use. You questions about the relationship is through DRI(declarative referential integrity) which means if a references b b must exist so primary key a becomes foreign key b so you can use a to delete b Cascade Delete and a to update b Cascade Update and a to set b to default Cascade Set Default and Null Cascade Set Null. If the above is confusing it is because it was created with a 26 pages long algebra and took many years to implement. If you have many references then you need a DRI trigger because the built in version will not go beyond three. Hope this helps.

http://www.databaseanswers.org/data_models/index.htm

http://www.databaseanswers.com/modelling_books.htm

|||Truthfully, I didn't really understand anything you said, maybe just a little when you talked about the Cascade Delete stuff. But it was all inference. Anyone else have any suggestions for my predicament?
|||

EDIT

You did not understand it because I did not say what you expect, what I am saying the only properties relating to tables is IDENTITY so all you have been trying to create is not working because it is not correct. And spend time with the links posted because that material is by Data Modeling experts.

|||

OK, as far as I understood you are talking about realtional inheritance. This is not automatically possible in SQL Server, you will (according to your sample) have to implement that on your own. Let me explain how I see your problem: You have two tables, one tables with the common values for properties (the parent table) and the specific table according to the type mentioned in the property table. If you query the table property you want SQL Server to pick either the child values from the one or the other child table, depending on the property type in the parent table. Unfortunately and as aletady mentioned, this is not implemented in SQL Server. Although a FK / PK raltionships can be implemented you will have to you aour own retrieval logic to get the data back. You can implement this usingeither client logic or server logic (like stored procedures). Hope that hits your question, if not do not hesitate coming back.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Connecting Sybase

Hi there

I want to pull data out of sysbase database.

Could you tell me which connection should I use to connect to sybase database?

Rahul Kumar

ny solution?

how to connect to Sybase in SSIS?

Connecting Sybase

Hi there

I want to pull data out of sysbase database.

Could you tell me which connection should I use to connect to sybase database?

Rahul Kumar

ny solution?

how to connect to Sybase in SSIS?

Connecting Surface Area Configuration to server on Clustered Server

I've run into a problem after upgrading production servers running under Microsoft Cluster Server, Windows Enterprise Server 2003 SP1, and SQL Server 2005 SP1 Enterprise Edition.

Once I completed the upgrade and applied the patch, I needed to examine the values set in the Features component of the Surface Area Configuration tool. The tool attempts to connect to localhost\instancename, but reports the following error:

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: SQL Network Interfaces, error:26 - Error Locating Server/Instance Specified)(Microsoft SQL Server)

I'm logged into the virtual server via Remote Desktop, but the SAC tool thinks I'm logged into the physical server supporting the virtual server. I seem to be caught between a rock and a hard place here, as I can't enable remote connections without first gaining access on the server itself.

Any help will be greatly appreciated.

Can anyone give me a little help here - I can't make configuration changes on production servers.

Thanks.

|||

Well, after a lucky break I found the answer.

sp_configure 'remote admin connections', 1
go
reconfigure
go

Once this was executed on the server I was able to go into Surface Area Configuration, enter the virtual server name, and connect successfully and make the other changes necessary. A simple solution to a scary problem. (Even though I'd used sp_configure for years on both Sybase and MSSQL servers it's easy to forget about the simple solutions.)

Connecting Surface Area Configuration to server on Clustered Server

I've run into a problem after upgrading production servers running under Microsoft Cluster Server, Windows Enterprise Server 2003 SP1, and SQL Server 2005 SP1 Enterprise Edition.

Any help will be greatly appreciated.

Can anyone give me a little help here - I can't make configuration changes on production servers.

Thanks.

|||

Well, after a lucky break I found the answer.

sp_configure 'remote admin connections', 1
go
reconfigure
go

|||thx a lot

Connecting Sqlserver2000 To Db400

Hello :

My name is Jose Moreno, i'm from Mexico, could you tell me if have information
about connecting SQLserver2000 to DB400 and if are there some restrictions?

sinceraly, thanks.

Ing. Jos Moreno Luna
Jos Benitez No. 2230 Col. Obispado
Monterrey N.L.
e-mail : jomo@.isisol.com.mx
Tel : 01(81) 83-46-98-30 , 83-46-52-11
Cel : 01(55) 50-68-21-03, 19-94-30-13

------------MS SQL Server 2000 can easily communicate with DB400 through ODBC.

We (my company) developed an insurance internett application communicating with a DB400. There are a couple of technical details that pop up. It worked, however, very well.

If you run into a particular problem, you may contact my. Perhaps I can help.

Regards
jarla
jarla@.netcom.no|||Can you please give me the details on how I can tranfer data from the AS 400 to my SQL Server 2000 database. Any links that you may have that provide this information would be helpful.

Originally posted by jarla
MS SQL Server 2000 can easily communicate with DB400 through ODBC.

We (my company) developed an insurance internett application communicating with a DB400. There are a couple of technical details that pop up. It worked, however, very well.

If you run into a particular problem, you may contact my. Perhaps I can help.

Regards
jarla
jarla@.netcom.no

connecting SQLExpress & Visual C# 2005

I have started to create a databse using SQL Server Management Studio Express, just a few tables, one with data. I now want to creat a some windows forms to populate the tables. However when I try to use the Add New Data Source wizard, I get an error when I try to create the connection string, but I have managed to connect to a copy of AdventureWorks located in the same parent directory.

Error is :
Unable to open the physical file "...". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
An attemp to attach an auto-named database for file ... failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

As I can connect to AdventureWorks, I presume Windows thinks the file is open, but I have no idea why, how to check, or how to close it.

TIA

Graham

Now Sorted not quite sure what I did, but I have downloaded some updates today, and I did start by looking at the data in Adveture works before I opened my project

Connecting SQL2005 from older C++ client - the solution was ...

After setting up
SQL2005express server with
mixed authentification I got the problem

1. I couldnt find a working ConnectionString
2. Studio2005 didnt connect to SQL any more

In the meantime I found that at least the following OLEDB-ConnectionString is working, when Im logged in as admin and can use Windows Autentication:

Provider='sqloledb'
Data Source='.' - without '/SQLEXPRESS' !!
Initial Catalog='MyDatabaseName'
Integrated Security='SSPI'

For details of SQL setup and code to test possible connetion strings
please see my homepage.
Martin
text of my question from 02 Dec 2005
I want to feed data of a C++ 6.0 project into SQL server and hoped that StudioExpress would give me the correct connection string in detail.
But my StudioExpress doesnt conntect to a SQL server configured for SQL-Authentication.
I made screenshote of the details an put them in subfolders of my homepage
http://home.arcor.SQL2005.
Does anybody know what exact connection string has to be used and which changes in StudioExpress AdvancedConnectionProperties are necessary to establish a working connection?

The above link contains a collection of screenshots with details of the problem.
In the meantime I did a standard setup of the whole StudioExpress2005 C# paket and put screenshots on my homepage where you can see the difference between standard setup and single SQLExpress setup in SQL-Authentication mode.
Martin

Connecting SQL2000 problem

Hi All,

I have a sqlserver 2000 & 2005 both installed on same server with the instance name as servername\sql2000 and servername\sql2005.

When i try to connect the sql2000 database from dotnet2002 or 2003, It comes up with an error as sql server doesnot exist or access denied but when i tried with dotnet2005, i am able to connect it.

I guess it has to do with an dotnet framework 1.0 & 1.1 but i am not sure where to look for it.

Can someone please point me to right direction ?

Thanks

Shaik Nagul

on the client, check that the TCP protocols are enabled using the Client Network Utility (cliconfig.exe) and on the server there is the analog Server Network Utility (svrnetcn.exe)|||

Hi,

Thanks for the reply.

I have checked on both client and server, the tcp/ip protocol is enabled and the default port is set to 1433.

But I am still unable to connect to sqlserver.

Anymore any ideas.

Thanks

|||

for a sanity check. . .

look at services control panel applet connected to the server.

does it say:

MSSQL$SQL2000

MSSQLSERVER$SQL2000

|||

I need to look closer at these posts - I bet it does say one of the above.

ok, are you using SQLClient in 2003? or OleDB?

what does your connection string look like for the 2003 connection?

SQL Authentication or SSPI?

|||

i am using sqlclient to access the database from both 2002 and 2003. My connection string is as

objCon.ConnectionString = "server=devserver\sql2000;database=northwind;uid=sa;pwd=;"

and in the services it does say

MSSQL$SQL2000

Thanks

|||

first compare that connection string to the other connection string.

isnt it supposed to look like:

Data Source=devserver\sql2000;Initial Catalog=northwind;Password=12abc12;User ID=sa;

are you running sql server authentication?

I bet there is an sa password you need (especially if this is a sp3a or above)

Do you have a windows domain? If you do, add your windows account to SQL Server as a db admin (Or make yourself a local administrator on the machine) and use the following:

Integrated Security=SSPI;Initial Catalog=northwind;Data Source=devserver\sql2000;

|||

Please refer to

http://blogs.msdn.com/sql%5Fprotocols/

There are several very useful articles for connectivity trouble-shooting.

It is really bizarre that you can connect using net2.0, but not net1.0. Since you can connect using net2.0 with the same connection string, the problem should be on the client side. To isolate the problem,

0. Is this remote connection or local connection?

1. please verify which connection provider is used by net2.0 by issuing following query after connected, "select net_library from sysprocesses where spid=@.@.spid". I would expect it is either TCP( remote ) or LPC (local).

2. Either case, I would like you to try more explicit connection string by replacing "devserver\sql2000" with "tcp:devserver:tcpport", and try connectioin using dotnet1.0.

You should be able to find the tcp port number that ss2k is listening in either server ERRORLOG or server side registry. According to your post, I assume TCP is enabled on both server and client, especially dotnet1.0 client.

for example, in server ERRORLOG

2006-01-17 11:54:53.86 server SQL server listening on XXXXXXXX: 1433.
2006-01-17 11:54:53.89 server SQL server listening on XXXXXXXX: 1433.

The registry key of TCP port should be located under

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\sql2000\MSSQLServer\SuperSocketNetLib\Tcp.

If you can connect, then there is an issue translating connection string. We need to take further action to diagnose the issue.

Please post your test results.

|||

As far as i think, the problem exist on the server because from the same machine i am able to connect to another sqlserver which doesn't have sql2005.

Thanks guys for trying to help but i have formatted the machine and reinstall the sqlserver2000 and now everything works fine. I would have not done this if there is no time constraint but since we are working on deadlines i have to do that.

Connecting Sql Servers Seperated by a firewall

Hello,
I have two sql servers that reside in seperate AD forests and that are
separated by a firewall. I need to enalbe connectivity between the two sql
servers. In addition to port 1433 are there any other ports that need to be
opened
There may be some additional ports besides TCP 1433 that you mean need to
open depending upon your situation. Please take a look at this KB article
which provides an exhaustive explanation,
http://support.microsoft.com/default...b;en-us;841249
Il-Sung
Il-Sung Lee
Program Manager -- SQL Server Protocols
Microsoft Corp.
This posting is provided "AS IS" with no warranties, and confers no rights.
"SteveB" <SteveB@.discussions.microsoft.com> wrote in message
news:DB639DA5-AAB2-46FE-BEF5-B3806B5C8E27@.microsoft.com...
> Hello,
> I have two sql servers that reside in seperate AD forests and that are
> separated by a firewall. I need to enalbe connectivity between the two sql
> servers. In addition to port 1433 are there any other ports that need to
> be
> opened

Connecting Sql Servers Seperated by a firewall

Hello,
I have two sql servers that reside in seperate AD forests and that are
separated by a firewall. I need to enalbe connectivity between the two sql
servers. In addition to port 1433 are there any other ports that need to be
openedThere may be some additional ports besides TCP 1433 that you mean need to
open depending upon your situation. Please take a look at this KB article
which provides an exhaustive explanation,
http://support.microsoft.com/defaul...kb;en-us;841249
Il-Sung
Il-Sung Lee
Program Manager -- SQL Server Protocols
Microsoft Corp.
This posting is provided "AS IS" with no warranties, and confers no rights.
"SteveB" <SteveB@.discussions.microsoft.com> wrote in message
news:DB639DA5-AAB2-46FE-BEF5-B3806B5C8E27@.microsoft.com...
> Hello,
> I have two sql servers that reside in seperate AD forests and that are
> separated by a firewall. I need to enalbe connectivity between the two sql
> servers. In addition to port 1433 are there any other ports that need to
> be
> opened

Connecting SQL Server2000 using Static IP over Internet

Hi Everybody

I am having same problem. I am trying to connect my vb6 application with SQL Server 2000. My database is on database server machine which has ID ADMIN and SQL Server is using default instance. Database instance name is DBPIMS. When I try to connect this database within any machine of LAN, it is working fine. But If I want to connect from my home or some other place through internet, it is not cannecting database to database.. I've 2wire router installed at my office. & I've done port forwarding for port 1433. Also I've one static IP which I am using for connection. My connection string is

"Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User ID=xxx;Initial Catalog=<atabase instance name;Data Source=" & static IP addres

But it couldn't get connected to database. Please can anybody help me. I am in urgent need to solve this as its been since long I am trying find its solution.

Thanks in advance

whitch is service pack instaled on your sql server?

Connecting Sql Server Yukon

Folks, i've just installed the express edition and have enabled all the protocols within the COMPUTER MANAGER. When i try connecting the INSTANCE through QA or EM, sql doesn't exist or access denied.
I've installed the default instance.
Please guide.a friend of mine had a similar problem,
check
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38264|||Hi, sorry for lack of knowledge. Can't i even connect to the db engine through QA. How do i interact with the database?
There is only SQL COMPUTER MANAGER in the Installation Menu.
There isn't any client tool under
C:\Program Files\Microsoft SQL Server\90\Tools\binn.

Please guide.|||There are no client tools for SQL Express. QA and EM don't work with it. You can use the command line utility included with it. You can also connect using .NET if you have web matrix or VS.NET.|||Thank you guys.

connecting SQL Server with oracle forms 9i/10g

Could anyone please guide me in detail to connect Oracle Forms 9i/10g with MS SQL Server database?
To connect with Oracle Database, we need to copy and paste the tnsnames.ora file from the Oracle Database folder to the Oracle Forms folder.
But how do i connect with other databases like SQL Server, MS Access, and so on?
Thanks in advance.From SQL server To Oracle
You have the security folder, see :
Console ROOT -> MS SQL SERVER -> servergroup -> (local) - > security - > linked server(right click here to add new linked server)
here you can connect to Oracle

if you want to query the oracle trough Sql.serv you have to use and openquery

so say you have an person table in Oracle and in the linked server you've called the linked server OrcConn your sql will look like this

An SELECT example:
SELECT * FROM OPENQUERY (OrcConn,
'SELECT Id, Name , age
From Person
where Name like ''Richard'' and id = 12')

An update Update example:

Update OPENQUERY (OrcConn,
'SELECT Id, Name , age
From Person
where Name like ''Richard'' and id = 12')
SET age =34

For a JOIN from SQL.Person to ORACLE.Person you do:

SELECT *
FROM OPENQUERY (OrcConn,
'SELECT Id, Name , age
From Person
where id = 12') as ORCPERSON
LEFT JOIN Person ON ORCPERSON.id = Person.Id
WHERE Person.Deleted = false

This will be "a bit slow" because you'll first query the oracle to fetch person then get it to your local level and requery the result to inner join|||Thanks for the reply.

I'm afraid you've replied not for my question.

Inorder to connect the Oracle Forms Builder we need to connect to a database. So if we want to connect to a MS SQL Server database, what's the procedure to do it?

I think now my question is bit more specific.

Thanks.

From SQL server To Oracle
You have the security folder, see :
Console ROOT -> MS SQL SERVER -> servergroup -> (local) - > security - > linked server(right click here to add new linked server)
here you can connect to Oracle

if you want to query the oracle trough Sql.serv you have to use and openquery

so say you have an person table in Oracle and in the linked server you've called the linked server OrcConn your sql will look like this

An SELECT example:
SELECT * FROM OPENQUERY (OrcConn,
'SELECT Id, Name , age
From Person
where Name like ''Richard'' and id = 12')

An update Update example:

Update OPENQUERY (OrcConn,
'SELECT Id, Name , age
From Person
where Name like ''Richard'' and id = 12')
SET age =34

For a JOIN from SQL.Person to ORACLE.Person you do:

SELECT *
FROM OPENQUERY (OrcConn,
'SELECT Id, Name , age
From Person
where id = 12') as ORCPERSON
LEFT JOIN Person ON ORCPERSON.id = Person.Id
WHERE Person.Deleted = false

This will be "a bit slow" because you'll first query the oracle to fetch person then get it to your local level and requery the result to inner join

Connecting SQL Server with Delphi 7

Hi,
How can i connect to an sql server db file. I have the server's ip number.
I'm using Delphi 7.
Thanks...
:)Can't you use BDE configuration tool in Control Panel?|||There are three easy methods in delphi7 u can choose:
1 BDE
2 ADO
3 DBExpress

Connecting sql server with asp.net

i have created an asp.net application with sql server2000 as backend in my localsystem.

when i deployed the project in other sytem in network,
i receive the following error message.
"sql server does not exsist or access denied"
when i create the same database in local system and change the data source name with local system in configuration file, the application is working.

i do not face this problem with vb.net application as it takes its datasource from network system.

is there anything like i have to give permission for asp.net in sql server network utility.

can anyone sort out the problem

tell me the connection string ?

|||

i have sorted out this issue.

The solution is go to secuity create a new login and give permission to remote server.

Connecting SQL server using JavaScript

I found an article on connecting SQL server using JavaScript. http://www.devarticles.com/c/a/JavaScript/Combining-North-Pole-with-South-Pole-JavaScript-with-SQL-Server-2000/

Do you people think it's possible? i try out the code already but something wrong with it. I just copy and paste it to a html file but it comes out with error. Do I miss out something? Thanks.

SY Tee

Hi,
Have you looked at www.connectionstrings.com?

Connecting SQL server to the net?

Hi all. Im using Visual Web developer 2005 and have successfully connected my SQL Server to Visual Web developer website. When i run it locally, its fine. But, i now would like the site to be placed on the net - but im not sure how to connect it properly in the webhost. Can someone tell me how this is done please, or a link? cheers.

You can add a connection string on web.config file

</connectionStrings>

then call this connection string on your page.

string strCon = System.Configuration.ConfigurationManager.ConnectionStrings["connectionname"].ConnectionString;

I hope this will help.

|||

Ok.....im still rather confused on how exactly the databse is linked. Firstly, i have the site on a external harddrive....but ive found that the SQL server databse is placed in a totally different location - C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\Data\Database.mdf

Surely i would have to link it some how for the site/databse to work properly on the site?

|||No ideas guys? Surely there must be a way so i can upload my SQL server database and connect it to my website? I can do it easily in Visual Web Developer in my computer....but stummped how to do it on the net.....|||

Hi,

You may follow the steps below.

First, backup you local database.

Second, get the physical folder of the database file exist on your server. From your previous post, it isC:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\Data\.

Third, restore your backup file to your server database. (Note, you have to replace theMove to Physical file nameitem in the Option tab with your real server-side database folder.)

Third, modify the connection string in your web.config file and call the string in your code by ConfigurationManager.

Thanks.

Connecting SQL Server Management Studio to an ODBC DSN

I'm not even sure if this is possible ... but here is my scenario:

We use a proprietary database. The database vendor wrote an ODBC driver which works well. So I have code that can connect to the database, do queries, etc. Can I use SQL Server Management Studio to connect to an ODBC DSN?

Ideally I would like to use the Object Browser and "Query Analyzer" (or whatever it's called) against the tables in this database.

Any help would be greatly appreciated.

Thanks.

-Aaron

No, SSMS only works against SQL Server 2000 and 2005.

The Tools forum is probably more appropriate for this question.

|||Moving the thread...

connecting sql server intalled on a persnal pc

Hi,

I'm trying to connect to sql server (installed on my personal PC) from a pocket pc using sqlconnection (Both are connected to my Router).

But every time I run my program on the pocket pc I get sqlexception when trying to open the connection.

I have already configured the sql server to accept remote connections...

1. what should I do?

2. Is it possible to open the sql server on my PC for remote connections (internet)?

3. How should I configure my Router so it wont block the connections?

1: Check this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=962226&SiteID=1 and this:

http://support.microsoft.com/kb/287932

also show us your connection string, as SqlClient from compact framework has some restrictions.

2: Yes, but it is not recommended.

3: See 1.

Connecting SQL Server Express.......uuhhhhh....

following is the error that I get:

"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 dows not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) "

I have following fields that I am supposed to fill:

1) Data Source : Defauld value "Microsoft SQL Server (SqlClient)"

2) Server Name: Empty - I am supposed to enter something. It has a button says Refresh. I entered localhost as you suggested, but not to avail.

3)Select or enter a database name: I am entrying SQLEXPRESS which is the default name of my SQL server. It is not working.

No, there is only one instance of the server.

I guess this information will help you to help me.....:-)

Have you used the surface area configuration tool to allow remote connections to SQL Server?

-Jamie

Connecting SQL Server Express 2005 to a Microsoft Access 97 Database

I want to use SQL Server to query an Access Database with about 40,000 rows of data. If possible, I don't want to upsize the database because others need acess to it in the ACC97 format. Is there a way to use ODBC to connect to the ACC97 database so that I can use the SQL query capability of SQL server to query the database.

I know access allows you to write some SQL queries but I need the power of the SQL server and now it is a matter of curiosity because I've been searching for this answer for about 8 hours.

WHat do you mean by power of SQL Server ? If you do some queries on the data and you will not pass the query to access as a pass through query, SQL Server will always pull out the whole data to do the query.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Connecting Sql Server 2005 on Windows 2003 using ASP local

Hello!
I'm migrating an IIS/SQL-Server application from Windows NT4.0 and SQL-
Server 2000 to Windows 2003 Server and SQL-Server 2005.

My problem is that it is not possible to connect local (IIS and SQL-
Server 2005 are runnng on the same node) using ODBC. Running the
applikation on a remote IIS (XPPro) all works fine. I can't see any
differences in the ODBC-configuration.
Any idea?

Thanks(kk035@.hotmail.com) writes:

Quote:

Originally Posted by

I'm migrating an IIS/SQL-Server application from Windows NT4.0 and SQL-
Server 2000 to Windows 2003 Server and SQL-Server 2005.
>
My problem is that it is not possible to connect local (IIS and SQL-
Server 2005 are runnng on the same node) using ODBC. Running the
applikation on a remote IIS (XPPro) all works fine. I can't see any
differences in the ODBC-configuration.

Error messages? Connection strings?

Running IIS and SQL Server on the same machine is not a very good idea,
from the point of view of performance and security. The latter is
particularly critical, if the web application is on the internet.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||It's just an internal application without any performance problems.

strConnect="DSN=ODBC-datasourcename;UID=username;PWD=password"

code snippet:

set conDb = Server.CreateObject("adodb.connection")
conDb.Open Session("strConnect")
for each errDb in conDb.Errors
if errDb.Number <0 then
bDbError = true
Session("strErrTxt1") = errDb.Description
Session("strErrTxt2") = errDb.Source
Session("strErrTxt3") = errDb.SQLState
Session("strErrNbr1") = errDb.Number
Session("strErrNbr2") = errDb.NativeError
exit for
end if
errDb.Clear
next

-err.Db contains no information|||(kk035@.hotmail.com) writes:

Quote:

Originally Posted by

It's just an internal application without any performance problems.
>
strConnect="DSN=ODBC-datasourcename;UID=username;PWD=password"
>
code snippet:
>
set conDb = Server.CreateObject("adodb.connection")
conDb.Open Session("strConnect")
for each errDb in conDb.Errors
if errDb.Number <0 then
bDbError = true
Session("strErrTxt1") = errDb.Description
Session("strErrTxt2") = errDb.Source
Session("strErrTxt3") = errDb.SQLState
Session("strErrNbr1") = errDb.Number
Session("strErrNbr2") = errDb.NativeError
exit for
end if
errDb.Clear
next
>
-err.Db contains no information

But what happens? Does it wait for 15 seconds and give up? Or does attempts
to use the connection fail?

And why use a DSN? Personally, I've never liked DSNs, it just one more
source of error. What is in that DSN?

What happens if you change the DSN part to Provider=SQLNCLI?

Where does the password come from? Is it hard-coded into the
application, or user-entered? Keep in mind that in SQL 2005, passwords
are always case-sensitive.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 17 Feb., 15:55, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(k...@.hotmail.com) writes:

Quote:

Originally Posted by

It's just an internal application without any performance problems.

Quote:

Originally Posted by

strConnect="DSN=ODBC-datasourcename;UID=username;PWD=password"

Quote:

Originally Posted by

code snippet:

Quote:

Originally Posted by

Quote:

Originally Posted by

-err.Db contains no information

>
But what happens? Does it wait for 15 seconds and give up? Or does attempts
to use the connection fail?
>
And why use a DSN? Personally, I've never liked DSNs, it just one more
source of error. What is in that DSN?
>
What happens if you change the DSN part to Provider=SQLNCLI?
>
Where does the password come from? Is it hard-coded into the
application, or user-entered? Keep in mind that in SQL 2005, passwords
are always case-sensitive.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

It seems that it does not wait for 15 seconds - it returns at once.
The password is hard coded. Changing to SQLNCLI doesn't change
anything. Using the same ASP-application on a remote IIS without
changing anything works fine. Using a wrong password in the ODBC-
Configuration to get additional configuration options produces an
entry in the error log of the SQL-Server, which tells that the
password is wrong. Using a wrong password in the DSN for the ASP-
application does not result in an error log entry.|||(kk035@.hotmail.com) writes:

Quote:

Originally Posted by

You are talking about the ODBC applet in the Control Panel?

Quote:

Originally Posted by

Using a wrong password in the DSN for the ASP-
application does not result in an error log entry.

A password in the DSN? Didn't you also have a password in the connection
string?

I still don't know why you think the connection failed.

If you leave out "if errDb.Number <0 then" do you get anything in
your Session array?

When you changed to SQLNCLI, did you remove the DSN reference?

What happens if you add "SERVER=bogus;" to the connection string?

What happens if you replace UID and PWD with "Integrated Security=SSPI;"?

Since I don't have to access to your system I have to ask questions in
the dark. Please keep this in mind when you answer the questions, and
try to be as clear and unambiguous as possible. It's enough of a guessing
game for me anyway.

Connecting Sql Server 2005 from ASP?

Hi
We are planning to move over to SQL Server 2005 in near future.

At the moment Website is on a seperate server then the Database.
OS for both the server is Window 2003 and currently our data is on SQL
Server 2000(on which everything works fine).

Part of the testing process we tried to connect our website on SQL 2005
and it does not work at all.

I get this

-
ADODB.Connection error '800a0e7a'
Provider cannot be found. It may not be properly installed.

When i use following connection string
"Provider=SQLNCLI;Server=127.0.0.1;Database=dbName;UID=UserName;PWD=UserPassword;"

(Above string has fixed problem for few people- googled it, but not for
me.)
-

-
This is the message i get for all the following connectiong string

Microsoft OLE DB Provider for SQL Server error '80004005'

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied.

1) - "Provider=SQLOLEDB.1;Password=UserPassword;Persist Security
Info=True;User ID=UserName;Initial Catalog=ABC;Data
Source=XXX.XX.X.XX,1433"

2) - "Provider=sqloledb;Data Source=XXX.XX.X.XX,1433;Network
Library=DBMSSOCN;Initial Catalog=ABC;User
ID=UserName;Password=UserPassword"

3) - "Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=UserName;Initial Catalog=ABC;Data Source = ServerName\SQLInstance"

I tried connecting to SQL Server 2005 through our system developed in
VB.Net 2003 and it works fine and here is the Connection String :

"Data Source=ServerName\SQLInstance;Initial Catalog=ABC;Persist
Security Info=False;user id=UserName;password=UserPassword"

Then i downloaded SQL Native Client and installed on the web server.

I looked in SAC Tool and "Local and Remote Connection" and "Using Both
TCP/IP and Named Pipes" options are selected. I have checked SQL
Browser service is running.

Now when i use this string

"Provider=SQLNCLI;Server=127.0.0.1;Database=DBName;UID=UserName;PWD=UserPassword;"

i get

Microsoft SQL Native Client error '80040e4d'

I have created this user and this user has proper permission to access
the database i need.

I would really appreciate any help as this is driving me crazy.

Mits

ASP has not been supported for some time. However, if you're stuck with legacy ASP code, I would expect you'd be able to connect, as it just connects via COM, I thought.

However, web development questions are off topic here. www.asp.net is the place for ASP.NET questions, but as I said, there is no support for asp, and no Microsoft website that allows asp questions. I percieve that the question relates to connecting to SQL Server 2005, which is on topic here, so I'm not locking the thread or marking it as off topic ( which I'd usually do for ASP questions ), but certainly the odds of people here using ASP are very low, so I'd recommend finding a forum that actually supports ASP and asking there. If you're using ASP.NET, and that's what you meant to say, please ask at www.asp.net.

Good luck !!!

|||hi cgraus
Thanks for not locking the topic.

I have never come across anywhere, where its says i cant connect to SQL Server 2005 using ASP. If this is true then that means we can't move on to SQL Server 2005 as all our website still written in ASP and the database is SQL Server 2000.

Mits

|||

// If this is true then that means we can't move on to SQL Server 2005

Well, I guess the real question is, why do you want to ? I mean, your website is ( sorry ) written in a hopelessly out of date technology, so how will SS2005 help you anyhow ? It's not like you can write .NET code to run in it...

But, and this is my core point, I don't know if it's true or not. I've not worked on ASP sites for years, and the odds of me doing it again are zero. That's why you'd be better off finding a site that suports ASP, because it's very much legacy technology now, and unless you're talking to people who are using it, the odds of you getting an answer are pretty low.

|||I understand your point about ASP and getting the answer or solution in this forum.
I will really appreciate if you can give me any link to Microsoft's website where it says that any website written in ASP won't be supported by SS2005.

Mits

|||What i am asking here or atleast tyring to get the answer is;

As it is possible to connect to SQL 2000 using ASP code, Is it still possible to do with SQL Server 2005?

Mits

|||

I don't know, and that's the core point I'm trying to make. If I had a link, I'd have answered your question, but the fact is that it's unlikely that anyone here uses asp, and so it's unlikely that anyone here can help you.

It's unfortunate that it's very hard to google for asp and not asp.net info. -net actually doesn't work, I've discovered.

You didn't answer my question tho - why do you need SQL Server 2005 ? What does it offer to make it worth the cost and the hassle ? I assume you have a huge site, to not be updating it to ASP.NET, but without .NET, what does SQL Server 2005 offer that's so important ?

|||Its a management decision and i am asked to evaluate SS2005 in our business environment.
Based on my findings management will take decision when to migrate over SS2005. Our website are E-Commerce sites and because of lack of resources the tranisition process from ASP to ASP.Net is slow.

I know this is not the place but in line of this discussion, as a MVP what factors would you consider if you want to migrate to SS2005. (Sorry i was just asking cgraus but if anyone wants to throw any ideas then they are more then welcome to do so.)

Mits

|||moved this post to SQL Server getting started.|||

My core criteria with any upgrade is 'what does it give me'. For example, I have projects written in VC6. I don't like VC6, not one bit. But, I don't upgrade my projects to VC2005, or even VC2002, unless I can see a compelling reason to. ANY upgrade to a new product is in essence a move to a new programming language. I would be surprised in T-SQL 2005 changes anything that would stop T-SQL 2000 code from working, but it's still an identifiable risk that I would not take unless there were actual SS205 features that I needed. SQL Server is also not cheap. It's not money I would spend if my SS2000 database was performing just fine, and all I intended to do was run the exact same database on a new engine. The exciting new stuff in SS2005 relate to managed code, and unless you're moving to ASP.NET and intend to use those features ( for good reason, not just to say you did ), I wouldn't bother thinking about moving.

|||hi

Good News guys got it all working.
I really appreciate all the effort and time put in by cgraus .

These are the steps i did, and i dont it might help anyone out there.

Our Webserver is outside DMZ on our network. It can only communicate
through secured channels on our linux firewall. I have got a named
Instance of SQL Server 2005(Enterprise Edition)

1) I punched a hole through our firewall that will allow communication
between our Webserver and DB server through specific Port.
2) I Installed SQL Native Client on Webserver.
3) In SSCM(SQL Server Configuration Manager) Tool
On Console Pane, Click Protocols for <Instance Name>
Double Click TCP/IP
Listen To All > No

For IP1
Active = Yes
Enabled = Yes
IP Address = IP of the DB Server
TCP = Blank/Nothing
TCP Port = 1433( or any port number but it has to be the same that
you have defined in firewall)

Save Everything and Re-start the SQL Server (Instance Name) Service.

This is the example of connection string that worked

Provider=SQLNCLI.1;Persist Security Info=False;User
ID=UserNamer;Password=UserPassword;Initial Catalog=NameofDatabase;Data
Source=DBServerName

Mits

Connecting Sql Server 2005 from ASP?

Hi
We are planning to move over to SQL Server 2005 in near future.

At the moment Website is on a seperate server then the Database.
OS for both the server is Window 2003 and currently our data is on SQL
Server 2000(on which everything works fine).

Part of the testing process we tried to connect our website on SQL 2005
and it does not work at all.

I get this

--------------------
ADODB.Connection error '800a0e7a'
Provider cannot be found. It may not be properly installed.

When i use following connection string
"Provider=SQLNCLI;Server=127.0.0.1;Database=dbName; UID=UserName;PWD=UserPassword;"

(Above string has fixed problem for few people- googled it, but not for
me.)
--------------------

--------------------
This is the message i get for all the following connectiong string

Microsoft OLE DB Provider for SQL Server error '80004005'

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied.

1) - "Provider=SQLOLEDB.1;Password=UserPassword;Persist Security
Info=True;User ID=UserName;Initial Catalog=ABC;Data
Source=XXX.XX.X.XX,1433"

2) - "Provider=sqloledb;Data Source=XXX.XX.X.XX,1433;Network
Library=DBMSSOCN;Initial Catalog=ABC;User
ID=UserName;Password=UserPassword"

3) - "Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=UserName;Initial Catalog=ABC;Data Source = ServerName\SQLInstance"

--------------------

I tried connecting to SQL Server 2005 through our system developed in
VB.Net 2003 and it works fine and here is the Connection String :

"Data Source=ServerName\SQLInstance;Initial Catalog=ABC;Persist
Security Info=False;user id=UserName;password=UserPassword"

I would really appreciate any help.

Mits(MVChauhan@.googlemail.com) writes:
> We are planning to move over to SQL Server 2005 in near future.
> At the moment Website is on a seperate server then the Database.
> OS for both the server is Window 2003 and currently our data is on SQL
> Server 2000(on which everything works fine).
>
> Part of the testing process we tried to connect our website on SQL 2005
> and it does not work at all.
> I get this
> --------------------
> ADODB.Connection error '800a0e7a'
> Provider cannot be found. It may not be properly installed.
> When i use following connection string
> "Provider=SQLNCLI;Server=127.0.0.1;

Did you install SQL Native Client on the web server? SQL Native Client
is a DLL that contains versions of both the ODBC SQL Server Driver
and the SQL Server OLE DB Provider that has been enhanced with support
for SQL 2005.

SQL Native Client is freely distributable.

> This is the message i get for all the following connectiong string
> Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> access denied.

Note that by default some editions of SQL 2005 by default only permits
local connections. You can modify this in the Surface Area Configuration
Tool.

You may also have to encure that the SQL Browser service is running.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland
Thanks for your reply.
I still cant conntect to SQL Server.

I downloaded SQL Native Client and installed on the web server.

I looked in SAC Tool and "Local and Remote Connection" and "Using Both
TCP/IP and Named Pipes" options are selected. I have checked SQL
Browser service is running.

Now when i use this string

"Provider=SQLNCLI;Server=127.0.0.1;Database=DBName; UID=UserName;PWD=UserPassword;"

i get

Microsoft SQL Native Client error '80040e4d'

I have created this user and this user has proper permission to access
the database i need.
Is there any special permissions that need? This is driving me crazy.

Mits

You may also have to encure that the SQL Browser service is running.

Erland Sommarskog wrote:

> (MVChauhan@.googlemail.com) writes:
> > We are planning to move over to SQL Server 2005 in near future.
> > At the moment Website is on a seperate server then the Database.
> > OS for both the server is Window 2003 and currently our data is on SQL
> > Server 2000(on which everything works fine).
> > Part of the testing process we tried to connect our website on SQL 2005
> > and it does not work at all.
> > I get this
> > --------------------
> > ADODB.Connection error '800a0e7a'
> > Provider cannot be found. It may not be properly installed.
> > When i use following connection string
> > "Provider=SQLNCLI;Server=127.0.0.1;
> Did you install SQL Native Client on the web server? SQL Native Client
> is a DLL that contains versions of both the ODBC SQL Server Driver
> and the SQL Server OLE DB Provider that has been enhanced with support
> for SQL 2005.
> SQL Native Client is freely distributable.
> > This is the message i get for all the following connectiong string
> > Microsoft OLE DB Provider for SQL Server error '80004005'
> > [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> > access denied.
> Note that by default some editions of SQL 2005 by default only permits
> local connections. You can modify this in the Surface Area Configuration
> Tool.
> You may also have to encure that the SQL Browser service is running.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||The SQL 2005 password is case sensitive while 2000 wasn't, so code that
works on SQL 2000 for logging in may not work on 2005.

MVChauhan@.googlemail.com wrote:
> Hi Erland
> Thanks for your reply.
> I still cant conntect to SQL Server.
> I downloaded SQL Native Client and installed on the web server.
> I looked in SAC Tool and "Local and Remote Connection" and "Using Both
> TCP/IP and Named Pipes" options are selected. I have checked SQL
> Browser service is running.
> Now when i use this string
> "Provider=SQLNCLI;Server=127.0.0.1;Database=DBName; UID=UserName;PWD=UserPassword;"
> i get
> Microsoft SQL Native Client error '80040e4d'
> Login failed for user 'UserName'.
> I have created this user and this user has proper permission to access
> the database i need.
> Is there any special permissions that need? This is driving me crazy.
>
> Mits
>
>
>
> You may also have to encure that the SQL Browser service is running.
> Erland Sommarskog wrote:
> > (MVChauhan@.googlemail.com) writes:
> > > We are planning to move over to SQL Server 2005 in near future.
> > > > At the moment Website is on a seperate server then the Database.
> > > OS for both the server is Window 2003 and currently our data is on SQL
> > > Server 2000(on which everything works fine).
> > > > > Part of the testing process we tried to connect our website on SQL 2005
> > > and it does not work at all.
> > > > I get this
> > > > --------------------
> > > ADODB.Connection error '800a0e7a'
> > > Provider cannot be found. It may not be properly installed.
> > > > When i use following connection string
> > > "Provider=SQLNCLI;Server=127.0.0.1;
> > Did you install SQL Native Client on the web server? SQL Native Client
> > is a DLL that contains versions of both the ODBC SQL Server Driver
> > and the SQL Server OLE DB Provider that has been enhanced with support
> > for SQL 2005.
> > SQL Native Client is freely distributable.
> > > This is the message i get for all the following connectiong string
> > > > Microsoft OLE DB Provider for SQL Server error '80004005'
> > > > [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> > > access denied.
> > Note that by default some editions of SQL 2005 by default only permits
> > local connections. You can modify this in the Surface Area Configuration
> > Tool.
> > You may also have to encure that the SQL Browser service is running.
> > --
> > Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> > Books Online for SQL Server 2005 at
> > http://www.microsoft.com/technet/pr...oads/books.mspx
> > Books Online for SQL Server 2000 at
> > http://www.microsoft.com/sql/prodin...ions/books.mspx|||I know there is no issue with Password.
I just realised one thing i dont know if it matters..
Between our webserver there is a firewall, do we need to punch a hole
that will allow communication between our webserver and DB Server.

Mits

pb648174 wrote:

> The SQL 2005 password is case sensitive while 2000 wasn't, so code that
> works on SQL 2000 for logging in may not work on 2005.
> MVChauhan@.googlemail.com wrote:
> > Hi Erland
> > Thanks for your reply.
> > I still cant conntect to SQL Server.
> > I downloaded SQL Native Client and installed on the web server.
> > I looked in SAC Tool and "Local and Remote Connection" and "Using Both
> > TCP/IP and Named Pipes" options are selected. I have checked SQL
> > Browser service is running.
> > Now when i use this string
> > "Provider=SQLNCLI;Server=127.0.0.1;Database=DBName; UID=UserName;PWD=UserPassword;"
> > i get
> > Microsoft SQL Native Client error '80040e4d'
> > Login failed for user 'UserName'.
> > I have created this user and this user has proper permission to access
> > the database i need.
> > Is there any special permissions that need? This is driving me crazy.
> > Mits
> > You may also have to encure that the SQL Browser service is running.
> > Erland Sommarskog wrote:
> > > (MVChauhan@.googlemail.com) writes:
> > > > We are planning to move over to SQL Server 2005 in near future.
> > > > > > At the moment Website is on a seperate server then the Database.
> > > > OS for both the server is Window 2003 and currently our data is on SQL
> > > > Server 2000(on which everything works fine).
> > > > > > > > Part of the testing process we tried to connect our website on SQL 2005
> > > > and it does not work at all.
> > > > > > I get this
> > > > > > --------------------
> > > > ADODB.Connection error '800a0e7a'
> > > > Provider cannot be found. It may not be properly installed.
> > > > > > When i use following connection string
> > > > "Provider=SQLNCLI;Server=127.0.0.1;
> > > > Did you install SQL Native Client on the web server? SQL Native Client
> > > is a DLL that contains versions of both the ODBC SQL Server Driver
> > > and the SQL Server OLE DB Provider that has been enhanced with support
> > > for SQL 2005.
> > > > SQL Native Client is freely distributable.
> > > > > This is the message i get for all the following connectiong string
> > > > > > Microsoft OLE DB Provider for SQL Server error '80004005'
> > > > > > [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> > > > access denied.
> > > > Note that by default some editions of SQL 2005 by default only permits
> > > local connections. You can modify this in the Surface Area Configuration
> > > Tool.
> > > > You may also have to encure that the SQL Browser service is running.
> > > > > > > --
> > > Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> > > > Books Online for SQL Server 2005 at
> > > http://www.microsoft.com/technet/pr...oads/books.mspx
> > > Books Online for SQL Server 2000 at
> > > http://www.microsoft.com/sql/prodin...ions/books.mspx|||MVChauhan@.googlemail.com (MVChauhan@.googlemail.com) writes:
> Thanks for your reply.
> I still cant conntect to SQL Server.
> I downloaded SQL Native Client and installed on the web server.
> I looked in SAC Tool and "Local and Remote Connection" and "Using Both
> TCP/IP and Named Pipes" options are selected. I have checked SQL
> Browser service is running.
> Now when i use this string
>
"Provider=SQLNCLI;Server=127.0.0.1;Database=DBName; UID=UserName;PWD=UserPass
word;"
> i get
> Microsoft SQL Native Client error '80040e4d'
> Login failed for user 'UserName'.

This is strange, because as I understood your original post, you were
connecting from a web server that was a on different machine from where
SQL Server is running.

However, in your connection string you have specified 127.0.0.1 which is
always the local machine. Still, you do make contact with an SQL Server
instance since you get that message.

Since I don't see your configuration, I'm left to guessworks, but there
are two obvious possibilities:
1) You have the username/password wrong.
2) You have an of SQL Server on the web server as well, but that's
not the one you intend to connect to.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hi Erland

I am sorry if i confued you, you are right WebServer and DB Server are
2 different machines. I have replaced 127.0.0.1 with the IP of DB
server and now the error is

TCP Provider: An existing connection was forcibly closed by the remote
host.

Configuration of Servers are as follows

Webserver
OS Windows 2003

Database Server
OS Windows 2003
DB SQL Server 2005 with Service Pack1

Mits

Erland Sommarskog wrote:

> MVChauhan@.googlemail.com (MVChauhan@.googlemail.com) writes:
> > Thanks for your reply.
> > I still cant conntect to SQL Server.
> > I downloaded SQL Native Client and installed on the web server.
> > I looked in SAC Tool and "Local and Remote Connection" and "Using Both
> > TCP/IP and Named Pipes" options are selected. I have checked SQL
> > Browser service is running.
> > Now when i use this string
> "Provider=SQLNCLI;Server=127.0.0.1;Database=DBName; UID=UserName;PWD=UserPass
> word;"
> > i get
> > Microsoft SQL Native Client error '80040e4d'
> > Login failed for user 'UserName'.
> This is strange, because as I understood your original post, you were
> connecting from a web server that was a on different machine from where
> SQL Server is running.
> However, in your connection string you have specified 127.0.0.1 which is
> always the local machine. Still, you do make contact with an SQL Server
> instance since you get that message.
> Since I don't see your configuration, I'm left to guessworks, but there
> are two obvious possibilities:
> 1) You have the username/password wrong.
> 2) You have an of SQL Server on the web server as well, but that's
> not the one you intend to connect to.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||MVChauhan@.googlemail.com (MVChauhan@.googlemail.com) writes:
> I am sorry if i confued you, you are right WebServer and DB Server are
> 2 different machines. I have replaced 127.0.0.1 with the IP of DB
> server and now the error is
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Configuration of Servers are as follows
> Webserver
> OS Windows 2003
> Database Server
> OS Windows 2003
> DB SQL Server 2005 with Service Pack1

Have you configured SQL 2005 to accept remote connections? The Developer
and Express Editions of SQL 2005 accepts by default local connection only.

Use the Surface Area Configuration tool to check and change this.

Also make sure that the SQL Server Browser service is running.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In SAC I have enabled Local and Remote Connection and also SQL Browser
service is running and is also Active.

Mits

Erland Sommarskog wrote:

> MVChauhan@.googlemail.com (MVChauhan@.googlemail.com) writes:
> > I am sorry if i confued you, you are right WebServer and DB Server are
> > 2 different machines. I have replaced 127.0.0.1 with the IP of DB
> > server and now the error is
> > TCP Provider: An existing connection was forcibly closed by the remote
> > host.
> > Configuration of Servers are as follows
> > Webserver
> > OS Windows 2003
> > Database Server
> > OS Windows 2003
> > DB SQL Server 2005 with Service Pack1
>
> Have you configured SQL 2005 to accept remote connections? The Developer
> and Express Editions of SQL 2005 accepts by default local connection only.
> Use the Surface Area Configuration tool to check and change this.
> Also make sure that the SQL Server Browser service is running.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||MVChauhan@.googlemail.com (MVChauhan@.googlemail.com) writes:
> In SAC I have enabled Local and Remote Connection and also SQL Browser
> service is running and is also Active.

And you did restart SQL Server after enabling remote connections?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Good News guys got it all working.
I really appreciate all the effort and time put in by Erland .

These are the steps i did, and i dont it might help anyone out there.

Our Webserver is outside DMZ on our network. It can only communicate
through secured channels on our linux firewall. I have got a named
Instance of SQL Server 2005(Enterprise Edition)

For IP1
Active = Yes
Enabled = Yes
IP Address = IP of the DB Server
TCP = Blank/Nothing
TCP Port = 1433( or any port number but it has to be the same that
you have defined in firewall)

Save Everything and Re-start the SQL Server (Instance Name) Service.

This is the example of connection string that worked

Provider=SQLNCLI.1;Persist Security Info=False;User
ID=UserNamer;Password=UserPassword;Initial Catalog=NameofDatabase;Data
Source=DBServerName

Mits

Erland Sommarskog wrote:

> MVChauhan@.googlemail.com (MVChauhan@.googlemail.com) writes:
> > In SAC I have enabled Local and Remote Connection and also SQL Browser
> > service is running and is also Active.
> And you did restart SQL Server after enabling remote connections?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Connecting SQL SERVER 2005 EXPRESS EDITION using Server Explorer

Hi all.

I use Visual Studio .NET 2002 and SQL Server 2005 Express Edition as database.

I try to connect to database using server explorer.

Test connection is OK. When I done with it and click OK, the message prompt that "Unable to connect to database".

So, is it impossible to connect SQL Server 2005 Express Edition using the server explorer?

But, the connection is ok when I connect through code cause I can retrieve and update database.

Hi azrina,

From your description I would guess, are you connecting to your sql express database both from your code and server explorer? and besides, you choose "auto attached" for your database file(.mdf), am I right?

If yes, i would say, well, since sql express use "custom instance" and there is a basic principle that one mdf file cannot be bound to more than 1 instance at the same time, so, if you are accessing your database file from your code, that particular file(say, dbname.mdf file) would be attached to your sql express custom instance. While if now you are connecting to the sql express again from server explorer, probably you will be not able to access that database file --most possibly you will get a "the database file has been already been attached" error.

Anyway, that's just my assumption and I use visual studio 2005 instead of vs.net 2002. And by the way, I've also encountered some kinds of troubles when connecting to my database from server explored--which sometimes I find is hard to descibe and explain.

Just hope my suggestion could help

|||Actually, what I am trying to do is to add database to the server explorer. As I want to use the DataSet, I can just drag and drop the sqlconnection and etc coz vs studio.net tools support the data access using data set. But, I think this version of my Visual Studio .Net does not support the SQL Server 2005 Express Edition. What can I do now is just do the data access thru code and not using the tools provided.

connecting sql server 2005 express beta

How do you connect to sql server 2005 express beta when using asp.netor how do you create databases or tables in it

Connecting SQL Server 2000 to legacy (AS/400) systems in a real time view?

Has anyone used SQL Server 2000 to attempt to get a real time view of a AS/400 system? Can you do it thru setting up a Linked Server?
Thanks,
Warren
Thanks Sue I will try this and let you know!
Thanks Again,
Warren

Connecting SQL Server 2000 to legacy (AS/400) systems in a real time view?

Has anyone used SQL Server 2000 to attempt to get a real time view of a AS/4
00 system? Can you do it thru setting up a Linked Server?
Thanks,
WarrenYes and yes.
Install Client Access on the SQL Server box and the
configure the linked server. For data source, use the IP
address of the AS400. For provider string, you need to
include the library you are using, connect timeout setting
and code page. There is some documentation for the settings
in the Client Access help files. You'd set the provider
string somewhat like:
InitCat=YourLibrary;CCSID=37;PCCodePage=
1252;
Data Source=xxx.xxx.xxx.xxx
Settings will depend on how your AS400 is configured. Again,
the Client Access help files have information on the
necessary connection string settings.
-Sue
On Tue, 6 Apr 2004 07:51:06 -0700, Warren
<anonymous@.discussions.microsoft.com> wrote:

>Has anyone used SQL Server 2000 to attempt to get a real time view of a AS/
400 system? Can you do it thru setting up a Linked Server?
>Thanks,
>Warren|||Thanks Sue I will try this and let you know!
Thanks Again,
Warren

Connecting SQL server 2000 from different domain.

Please help to connect SQL server 2000 on different domain.

Please correct if I m wrong.

System details as follows

Client

Domain : DomainA

System: System1

IP Address : 10.10.225.19

SQL server 2000 installed

SQL Server

Domain : DomainB

System: System2

IP Address : 10.108.22.19

SQl Server : System2\inst2 ( My database is under inst2 instance)

SQL login : TestUser1

Password : Users123

I have Opened firewall port to access 10.108.22.19 from 10.10.225.19

While connecting the SQL server “System2\inst2” from the client machine enterprise manager with the SQL authentication, I am getting error Login failed for user “TestUser1”

And I have noticed in the SQL server log the login attempt shows under System2 default instance ( no instance). Actually I want to connect system2\inst2.

Please let me know the steps to connect.

Thanks in advance.

For named instance, you also need to open UDP port 1434 for firewall on 10.108.22.19(the server). This is requried to find named instances. You said "default instance (no instance)", which errorlog were you checking?|||

Thanks for your reply,

I got solution and i am able to connect all my SQL servers from my domain.

It is a firewall issue , buy default they opened for 1433 it is goes to Default instance , but i need to connect a different SQL server instance.

then i opened firewall for that port it is connecting .

Thanks

connecting sql server

Thanks
Hari
MCDBA
"e-mid" <someone@.somewhere> wrote in message
news:OFRlQt6VEHA.2408@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> i am becoming insane.
> i did not change even a letter but it is working now ?!
>
> "e-mid" <someone@.somewhere> wrote in message
> news:u7BQSe6VEHA.2520@.TK2MSFTNGP12.phx.gbl...
> database:
IsAutoUpdateStatistics[vbcol=seagreen]
> an
are[vbcol=seagreen]
too.
>

connecting sql server

i created a database, when i query a simple select statement , i get an
error:
"Cannot open database requested in login 'XYZ_Database'.
Login fails, Login failed for user 'sa''
But when i change , the database name to Northwind, it works.
i checked that user rights of 'sa' for XYZ_database and Northwind are the
same?
i get the same error, when i used integrated security to connect too.
what would be the case here?Hi,
It seems the database creator/owner might be some other user.
Execute the below command to check the database owner:-
sp_helpdb 'XYZ_Database'
If the owner is a non SA user , then change the database owner using:-
sp_changedbowner system procedure (Refer books online for information)
Thanks
Hari
MCDBA
"e-mid" <someone@.somewhere> wrote in message
news:#uip9U6VEHA.208@.TK2MSFTNGP10.phx.gbl...
> i created a database, when i query a simple select statement , i get an
> error:
> "Cannot open database requested in login 'XYZ_Database'.
> Login fails, Login failed for user 'sa''
>
> But when i change , the database name to Northwind, it works.
> i checked that user rights of 'sa' for XYZ_database and Northwind are the
> same?
> i get the same error, when i used integrated security to connect too.
> what would be the case here?
>|||Hari;
user of the database seems to be sa; here is the properties of the database:
"XYZ_database 3.00 MB sa 7 Jun 21 2004 Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL,
Version=539, Collation=Latin1_General_CI_AS, SQLSortOrder=0,
IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics
80"
what else can be the problem?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#Sh3WY6VEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Hi,
> It seems the database creator/owner might be some other user.
> Execute the below command to check the database owner:-
> sp_helpdb 'XYZ_Database'
> If the owner is a non SA user , then change the database owner using:-
> sp_changedbowner system procedure (Refer books online for information)
> Thanks
> Hari
> MCDBA
>
>
> "e-mid" <someone@.somewhere> wrote in message
> news:#uip9U6VEHA.208@.TK2MSFTNGP10.phx.gbl...
> > i created a database, when i query a simple select statement , i get an
> > error:
> >
> > "Cannot open database requested in login 'XYZ_Database'.
> > Login fails, Login failed for user 'sa''
> >
> >
> > But when i change , the database name to Northwind, it works.
> >
> > i checked that user rights of 'sa' for XYZ_database and Northwind are
the
> > same?
> >
> > i get the same error, when i used integrated security to connect too.
> >
> > what would be the case here?
> >
> >
>|||i am becoming insane.
i did not change even a letter but it is working now '!
"e-mid" <someone@.somewhere> wrote in message
news:u7BQSe6VEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hari;
> user of the database seems to be sa; here is the properties of the
database:
> "XYZ_database 3.00 MB sa 7 Jun 21 2004 Status=ONLINE,
> Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL,
> Version=539, Collation=Latin1_General_CI_AS, SQLSortOrder=0,
> IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics
> 80"
> what else can be the problem?
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:#Sh3WY6VEHA.1144@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > It seems the database creator/owner might be some other user.
> >
> > Execute the below command to check the database owner:-
> >
> > sp_helpdb 'XYZ_Database'
> >
> > If the owner is a non SA user , then change the database owner using:-
> >
> > sp_changedbowner system procedure (Refer books online for information)
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> > "e-mid" <someone@.somewhere> wrote in message
> > news:#uip9U6VEHA.208@.TK2MSFTNGP10.phx.gbl...
> > > i created a database, when i query a simple select statement , i get
an
> > > error:
> > >
> > > "Cannot open database requested in login 'XYZ_Database'.
> > > Login fails, Login failed for user 'sa''
> > >
> > >
> > > But when i change , the database name to Northwind, it works.
> > >
> > > i checked that user rights of 'sa' for XYZ_database and Northwind are
> the
> > > same?
> > >
> > > i get the same error, when i used integrated security to connect too.
> > >
> > > what would be the case here?
> > >
> > >
> >
> >
>|||Good. :)
Thanks
Hari
MCDBA
"e-mid" <someone@.somewhere> wrote in message
news:OFRlQt6VEHA.2408@.tk2msftngp13.phx.gbl...
> i am becoming insane.
> i did not change even a letter but it is working now '!
>
> "e-mid" <someone@.somewhere> wrote in message
> news:u7BQSe6VEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > Hari;
> >
> > user of the database seems to be sa; here is the properties of the
> database:
> >
> > "XYZ_database 3.00 MB sa 7 Jun 21 2004 Status=ONLINE,
> >
> > Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL,
> >
> > Version=539, Collation=Latin1_General_CI_AS, SQLSortOrder=0,
> >
> > IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics
> > 80"
> >
> > what else can be the problem?
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:#Sh3WY6VEHA.1144@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > It seems the database creator/owner might be some other user.
> > >
> > > Execute the below command to check the database owner:-
> > >
> > > sp_helpdb 'XYZ_Database'
> > >
> > > If the owner is a non SA user , then change the database owner using:-
> > >
> > > sp_changedbowner system procedure (Refer books online for information)
> > >
> > > Thanks
> > > Hari
> > > MCDBA
> > >
> > >
> > >
> > >
> > > "e-mid" <someone@.somewhere> wrote in message
> > > news:#uip9U6VEHA.208@.TK2MSFTNGP10.phx.gbl...
> > > > i created a database, when i query a simple select statement , i get
> an
> > > > error:
> > > >
> > > > "Cannot open database requested in login 'XYZ_Database'.
> > > > Login fails, Login failed for user 'sa''
> > > >
> > > >
> > > > But when i change , the database name to Northwind, it works.
> > > >
> > > > i checked that user rights of 'sa' for XYZ_database and Northwind
are
> > the
> > > > same?
> > > >
> > > > i get the same error, when i used integrated security to connect
too.
> > > >
> > > > what would be the case here?
> > > >
> > > >
> > >
> > >
> >
> >
>

Saturday, February 25, 2012

Blog Archive

About Me