Have working web-app & db on hoster, but need to set it up on localhost now

Question

How do I connect my web-app and db using localhost (MSSQL 2005, VB 2003, .net 1.0, Windows XP Pro)?

Everything works fine on my hoster. But, now I'm revisiting code after a couple of years away and need to set it up to work on my laptop for demos and new development. I have a demo Friday morning, I hope I can get this running locally, since I do not think I'll have internet access for the demo.

Current error message: "Login failed for user 'pseudoid'. The user is not associated with a trusted SQL Server connection."

Current connection string: "Provider=SQLOLEDB;user id=pseudoid;password=pseudopw;initial catalog=pseudocat;data source=localhost"

I already have the db on my laptop and my VB development environment running. I just need to connect the two.

Answer 1

Your error message indicates that your SQL Server instance is not set up to accept SQL Logins. You can either:
1. Change your database instance to accept SQL Logins (In SSMS utility, in the Object Explorer pane, right-click on your instance and it's in the Security tab.
2. Remove the user id and password from your connection string and use "integrated security=sspi" in the connection string instead.

Hope this helps,
Bob Beauchemin
SQLskills

Answer 2

Sorry, neither option worked for me.

Your option 2 ended up with this error message: "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied." My connection string was this: "Provider=SQLOLEDB;integrated security=sspi;initial catalog=eitogo;data source=localhost"

I must say, this is the same message I now get when I leave in the original user id and password, since I have rebuilt the app. I was thinking the web.config file was a run-time parameter, but it appears to be a compile-time parameter---sorry.


On your option 1, I'm really new to the old MSSQL 2005, so I may not be doing what you said. I have only one database, and a right click on that database does not produce anything for security. If I left click, a security item does appear in the Object Explorer tree, but I see nothing in it about SQL logins.

On my server instance I do see a security node on the tree, which has a login node under it. Everything, except "sa", has disabled the SQL Server Authentication. If I right click on Security.Logins node a new screen comes up, but I'm not sure what do do with it.

Answer 3

Hi Brent,

If you are not sure of creating a SQL login through GUI ,
you may run the below query in new query window..But to run this query you may have to be atleast in the securityadmin serverrole.

USE

MASTER

GO

CREATE

LOGIN [pseudoid] WITH PASSWORD=N'pseudopw',

DEFAULT_DATABASE

=[pseudocat], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE

PSEUDOCAT

GO

CREATE

USER [pseudoid] FOR LOGIN [pseudoid] WITH DEFAULT_SCHEMA=[dbo]

go

sp_addrolemember

'db_owner','pseudoid'

go


-- Then try to use your first connection string.

---

Thanks, Leks

Answer 4

I really hate it when I don't know what I'm doing. Since I've been away from this project for a couple of years, the 2005 development environment is totally new to me...and I have a demo tomorrow. Grr...

I used SSMS's Object Explorer pane to highlight this node in the tree: ...Databases.eitogo.Security.Roles.Database Roles.db_securityadmin

I clicked "New Query" and cut-and-pasted the query above. After replacing 'pseudoid' with 'wbbradshaw' and 'pseudocat' with 'eitogo', I clicked Execute and got :

     Msg 15023, Level 16, State 1, Line 3

     User, group, or role 'wbbradshaw' already exists in the current database.

(I also replaced any other pseudoxxx's (like pseudopw) with the real data, but I did not replace [us_english] or [dbo].)


When I tried running the program I got:

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

 

Answer 5

Hi brent,

Leave out the security admin part as you have enough rights to create a uner and login.
I think the logins are already in place, may be you can delete both user and login in the name which is used in the script and try to run the query once again.
You dont ned to change english / dbo.

---

Thanks, Leks

Answer 6

Sorry. I don't know what you mean by "leave out the security admin part". I re-ran the query without either or both commented-out areas, but only when I commented out both at the same time did I get no errors. Here's the query:

"USE
MASTER
GO

--CREATE
--LOGIN wbbradshaw WITH PASSWORD=N'xx...',
--DEFAULT_DATABASE=eitogo,
--DEFAULT_LANGUAGE=[us_english],
--CHECK_EXPIRATION=OFF,
--CHECK_POLICY=OFF
--GO

USE
eitogo
GO

--CREATE
--USER wbbradshaw FOR LOGIN wbbradshaw WITH DEFAULT_SCHEMA=[dbo]
--go

sp_addrolemember
'db_owner','wbbradshaw'
go
"

When I run the app without userid and password in ConnectionString I get: "Invalid authorization specification "

When I run the app with userid and password in ConnectionString I get: "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied "

Answer 7

Now i think , we have to check whether the SQL services are running . If that looks fine , then we have to look at connection string ,
"Provider=SQLOLEDB;user id=pseudoid;password=pseudopw;initial catalog=pseudocat;data source=localhost"

-- If you are using .net , then i dont think provider is needed in your connection string as .net has its own data provider class to connect to SQL.
typically use this,

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

---

Thanks, Leks

Answer 8

How do I tell if the SQL services are running? If it's not running, how do I start it?

I tried the connection string without Provider, and it didn't like it. It's error message specifically said it needed Provider and gave the SQLOLEDB as an example.

Answer 9

RUN-->SERVICES.MSC --> check for SQL SERVER (MSSQLSERVER) and start it , if not started earlier.

---

Thanks, Leks

Answer 10

SQL Server (MSSQLSERVER) is Started and it's start-up type is Automatic.

SQL Server Agent (MSSQLSERVER) is not started and it's start-up type is Manual.

Answer 11

Can you try with your IP address in the data source instead of local host

---

Thanks, Leks

Answer 12

How do I find my IP address? Sorry for my being so dumb.

Answer 13

Go to MY COMPUTER and right click and see your computer name – note the name of your system

Now again run --> cmd --> ping yoursysname

---

Thanks, Leks

Answer 14

None of the following values for Data Source worked. They all gave me: "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied "
By the way, when you said "run-->cmd-->ping yoursysname", I assume that yoursysname is "computer name". I saw nothing for system name.

And, for what it's worth, the ping request timed out. It said: "Pinging <yoursysname>.domain_not_set.invalid [xx.yyy.zz.202] ... sent 4 ... lost 4"

So, I got my computer name. It is the same as my SQL server name. I pinged it, but got one of two different IP's each time. I noticed in SSMS an IP-like number and my computer name with a \user appended to it.

So, for the five Data Source values in my connection string I tried the two IP names from ping, my computer name from MyComputer, the IP-like number from SSMS, and the extended computer name (with \user appended to computer name) from SSMS.


Sorry, I'm still not running.

Answer 15

Go into the surface area configuration and set the remote connections and local connections to both TCP/IP and Named Pipes and restart the server. 

---

Thanks, Leks

Answer 16

Still doesnt' work. And, the ping timed out four out of four times. I restarted the sever by right-clicking on the server node in the SSMS Object Explorer pane and selected Restart. I did not restart the OS.

Four of five the Data Source strings gave this error: "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied "

If Data Source=localhost the error changed to: "Login failed for user 'wbbradshaw'. The user is not associated with a trusted SQL Server connection "

Any more ideas?

Answer 17

Login manually with SSMS in to SQL server using wbbradshaw login and see whether the login is working and able to create a connection to sql server.

---

Thanks, Leks

Answer 18

Sorry again, but how do I do that?

You'd think with all the development work I did remotely through .net and ado and the 2003 Enterprise Manager that I'd know how to do this, but I don't.

Answer 19

RUN --> SSMS --> tpye in LOCAL in the server name , set sql authentication and then supply the uid and password as same in the conenction string and see what is the error there ?

---

Thanks, Leks

Answer 20

SSMS is SQL Server Management Studio, which you should be able to find at Start->Programs->Microsoft SQL Server 2005->SQL Server Management Studio. If it isn't there, then you need to install that part of the client tools.

I got a bit lost in the back-and-forth in the thread above - what was the error message you were hitting? It looked like at first it was a Login Failed message - if that is the case, can you look in the SQL Server ERRORLOG file (at c:\program files\microsoft sql server\INSTANCEID\mssql\log\ERRORLOG) for Login Failed messages and post them up here?

---

This post is provided 'as is' and confers no express or implied warranties or rights.

Answer 21

I just figured out what Lekss meant by RUN-->SSMS-->type in LOCAL... He meant to shut down my currently running SSMS and run SSMS from the beginning, where a dialog box comes up asking for the rest of the information he mentioned in his note.

So, here is the error message, doing what Lekss asked for:

Cannot connect to YOUR-7745395E08.
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476


I have verified that the server is set up for both local and remote connections, using both TCP/IP and named pipes.

So, now what do I do?

Answer 22

See my reply to Lekss's message.