SQL Server Express 2005 problem *SOLVED*

Forums › Microsoft Windows › SQL Server Express 2005 problem *SOLVED*

PIERCD

58 posts

Master Geek

Topic # 17897 17-Dec-2007 09:08

Hey Geekzoners,

Im sure there is an SQL Server guru out there who will be able to go...oh you nub, you just need to execute this script. And if there is, please tell me what it is, cause I am really stuck on this one.

We are running a small dedicated workstation here (XP 64) which we would like to host our MS Business Contact Manager database on. I have set up SQL Express 2005, and used the MS BCM tool to create the Database, I can log into it fine, but I am also the database owner. Other members on the network are having no success, and I have tried giving them all as many access privledges as I can possibly see, all to no avail.

what privledges to I "have" to give them to connect and modify this database, in both the SQL server setup, and the database setup.

Many Thanks in advance

freitasm

BDFL - Memuneh
63040 posts

Uber Geek
+1 received by user: 13612

Administrator

Trusted

Geekzone

Lifetime subscriber

Reply # 100370 17-Dec-2007 09:14

Is this an existing database you reloaded from a backup? If so is this also a new user on this instance?

Backblaze cloud backup (personal and business) | Geekzone broadband switch | Amazon (Geekzone aff) | MightyApe (Geekzone aff) |

My technology disclosure | Business Transformation | Enterprise Content Management | Customer Relationship Management | Sharesies investment fund

PIERCD

58 posts

Master Geek

Reply # 100372 17-Dec-2007 09:19

This is a new database, so all users will be new.

freitasm

BDFL - Memuneh
63040 posts

Uber Geek
+1 received by user: 13612

Administrator

Trusted

Geekzone

Lifetime subscriber

Reply # 100373 17-Dec-2007 09:21

Ok, I thought it would be a permissions problem from an old database - it seems it is not. So let's see if any guru shows up...

Backblaze cloud backup (personal and business) | Geekzone broadband switch | Amazon (Geekzone aff) | MightyApe (Geekzone aff) |

My technology disclosure | Business Transformation | Enterprise Content Management | Customer Relationship Management | Sharesies investment fund

freitasm

BDFL - Memuneh
63040 posts

Uber Geek
+1 received by user: 13612

Administrator

Trusted

Geekzone

Lifetime subscriber

Reply # 100374 17-Dec-2007 09:21

Also, you mean you can acccess it locally, but users on the network cannot? Have you enabled the TCP access for this instance?

Backblaze cloud backup (personal and business) | Geekzone broadband switch | Amazon (Geekzone aff) | MightyApe (Geekzone aff) |

My technology disclosure | Business Transformation | Enterprise Content Management | Customer Relationship Management | Sharesies investment fund

PIERCD

58 posts

Master Geek

Reply # 100375 17-Dec-2007 09:26

I can access it over the network, I got the TCP side of things all running (that seems to be the first trap as BCM runs on a different port) but still no other users can.

JamesHip

25 posts

Geek
+1 received by user: 1

Trusted

Reply # 100428 17-Dec-2007 14:31

I'm not really offering a solution, more like a checklist.

SQL Server Express "wakes up" and attaches to the database file on the fly. This is vastly different from SQL Server proper, which maintains a permanent connection to the database.

I not sure, but I assume the MS-BCM application runs as a Windows app. And since you can connect over the network, then I also assume that there is a remote connection in place. Therefore, can you edit the connection string in the app.config file? It should be something like:

Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;

James Hippolite
BCA, MCP, MCSD.NET, MCITP, MCPD, MCT

PIERCD

58 posts

Master Geek

Reply # 100436 17-Dec-2007 15:17

I am 99% sure this isnt a client side issue, BCM runs as an add-in to Outlook, there really arent a lot of config options available when setting up, it is pretty much input the name of the server you are running the SQL on and if you have the ports and users set up it connects very sweetly.

As it is the other clients are identifying that the server exists, but are either not recognizing that SQL server is running, or that there is a BCM database running on that server.

chakkaradeep

799 posts

Ultimate Geek

Trusted

Reply # 100445 17-Dec-2007 15:59

PIERCD:
I have set up SQL Express 2005, and used the MS BCM tool to create the Database, I can log into it fine, but I am also the database owner. Other members on the network are having no success, and I have tried giving them all as many access privledges as I can possibly see, all to no avail.

Windows authentication or SQL authentication ?

And also can you brief the process how you gave them access? Just to check it.

Regards,
Chaks

Desktop : Intel Quad Core Q9400 2.66GHz - 8GB RAM - 500 GB + 500 GB HDD - NVidia GeForce 9800GT - LG246WH Flatron Display - Windows Server 2008 R2 Enterprise with Hyper-V
Virtual Machine : Powered by Hyper-V and VMWare Workstation
Laptop: HP dv7-3004TX Entertainment Notebook PC | HP Touchsmart tx2 1119au - Windows 7 Ultimate x64
Mac: iMac 21.5" Snow Leopard
Mobile : iPhone 3GS

PIERCD

58 posts

Master Geek

Reply # 100451 17-Dec-2007 16:21

Windows Authentication, as per all the docs I could find on the matter, this is the correct choice.
I have changed the TCP/IP Port in the Network Protocols to 5356 as per those same docs.

So I have created User accounts on the server, and then added those accounts to the SQL Server Permissions. Having Connect SQL, View any database and View server state ticked.

I have then gone to the MSSmallBusiness Database itself and added the users there, and given them the dbo profile

Im pretty sure its something in that process which has gone wrong.

chakkaradeep

799 posts

Ultimate Geek

Trusted

Reply # 100471 17-Dec-2007 17:26

PIERCD: Windows Authentication,
So I have created User accounts on the server, and then added those accounts to the SQL Server Permissions. Having Connect SQL, View any database and View server state ticked.

Ok, open your SQL Server Manager, go to the Business Contact Manager Database-->Security-->Users, can you see all the users you gave permission for that database?

And if you have a higher version, SQL Server Developer or SQL Server Enterprise, open the SQL Server Profiler to see what is happnening. But I think you are working on SQL Express.

PIERCD

58 posts

Master Geek

Reply # 100588 18-Dec-2007 08:43

Yep I can see the Users in there, and yes I am running SQL Express.

Strangely I can connect the other users via the sqlcmd in command prompt, so maybe this is a BCM issue.

chakkaradeep

799 posts

Ultimate Geek

Trusted

Reply # 100591 18-Dec-2007 09:02

PIERCD: Yep I can see the Users in there, and yes I am running SQL Express.

Strangely I can connect the other users via the sqlcmd in command prompt, so maybe this is a BCM issue.

So, you say if you connect to the BCM database via the sqlcmd command as a user, you can and it fails when from connecting from BCM ? If so, then I would suggest you to double check at the BCM side. I have not used BCM, so I cant help there

Just to be sure, right click on the user (under the menu travrsal in my previous post), choose properties and check whether what you want is/are selected for permissions.

PIERCD

58 posts

Master Geek

Reply # 100592 18-Dec-2007 09:28

OK maybe you could just confirm something about the sqlcmd command for me, just to make sure I am actually connecting.

sqlcmd -S "tcp:SERVER\SQLSERVER\MSSmallBusiness,5356"

and this returns a

1>

rather than errors, so I am assuming that is a successful connection.

If so that is real frustrating, because there are absolutley no config options anywhere I can see to make sure BCM connects.

PIERCD

58 posts

Master Geek

Reply # 100616 18-Dec-2007 10:43

Thanks all for your help, but Ive got it now, seems it came down to a dodgy installation of SQL Express (Its always those simple things) Re-Installed and it worked without a hitch, bizzare indeed.

chakkaradeep

799 posts

Ultimate Geek

Trusted

Reply # 100619 18-Dec-2007 10:51

PIERCD: OK maybe you could just confirm something about the sqlcmd command for me, just to make sure I am actually connecting.

sqlcmd -S "tcp:SERVER\SQLSERVER\MSSmallBusiness,5356"

and this returns a

1>

rather than errors, so I am assuming that is a successful connection.

If so that is real frustrating, because there are absolutley no config options anywhere I can see to make sure BCM connects.

Checkout the sqlcmd -? for more arguments help

Here is what you need to do,

in command prompt,

C:\>sqlcmd -S . -U uname -P pwd
1>use bcmdatabase;
2>select top(2) * from table_name;
3>go

And the above will display records.

There will be error message if the login fails. If you dont specify user name and password, it will default to Windows authentication mode.

The . specifies localhost sql server. Replace the . to your sql server