Member Avatar for jlivvers

Hi,

I have recently finished a windows desktop application for a client that connects to a sql server database. Throughout development while the server instance was on my local machine the application and server communicated fine with the connection string below

<add name="myString" 
         connectionString="Data Source=myServerName;Initial Catalog=&quot;myDbName&quot;;Integrated Security=True" providerName="System.Data.SqlClient" />

However when we installed the database onto a server and swapped the data source names over the application failed to access the server.

When we installed sql server we used the default settings except when it came to the service account settings where the DBA used checked use a domain user account and used the administrator log in details. Everything else was by default.

The version of sql server is 2005 developer edition.

Apologies for any holes in my knowledge - I've never tackled this task before!

Can anyone shed any light?

Edited by jlivvers because: n/a
  • 2 Contributors
  • 1 Reply
  • 220 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by BitBlt

Dani AI

Generated

Brief expert summary and checklist expanding on ’s tip for : using Integrated Security means the client’s Windows identity is used — that identity (or an AD group containing it) must exist as a SQL Server login and be mapped to the database with the right role(s). Add Windows logins or, better, an AD group under Security → Logins and grant only the minimum database roles needed. (learn.microsoft.com)

SQL Server 2005 setup often blocks remote access by default (Developer/Express/Evaluation installs commonly require you to enable it). Use the SQL Server Surface Area Configuration (or SSMS Server Properties → Connections) to allow “local and remote” connections, then enable TCP/IP in SQL Server Configuration Manager and restart the instance so the change takes effect. (learn.microsoft.com)

Named-instance vs default-instance networking and firewall rules are a very common root cause. Default instances normally use TCP 1433; named instances use dynamic ports (or a configured static port). Either start SQL Server Browser (clients ask it on UDP 1434) or configure a fixed TCP port and open that in the server firewall. Quick remote test from a client is useful, for example with the sqlcmd client: 

sqlcmd -S tcp:ServerName,1433 -E

If that connects, the network/protocol layer is OK. (learn.microsoft.com)

If Integrated Security still fails after the above, check for Kerberos/SPN issues — especially because the DBA used a domain account for the service. Symptoms like “Cannot generate SSPI context” or logins appearing as NT AUTHORITY\ANONYMOUS LOGON point to SPN/name-resolution problems; either let SQL register the SPN (requires appropriate rights) or have AD admins register it using SetSPN, or use the Kerberos Configuration Manager to diagnose. Finally, prefer AD groups over individual accounts and avoid running SQL under overly privileged domain accounts. (ftp.zx.net.nz)

Summary checklist: create/mapping Windows logins; enable remote connections + TCP/IP; start SQL Browser or set static port; open the port(s) in Windows firewall; test with sqlcmd/SSMS; inspect SPN/SSPI if Windows auth still fails. These steps resolve the vast majority of SQL Server 2005 remote-connect problems.

Member Avatar for BitBlt

First things first...if you're using integrated security, did you add the users' NT logins to SQL Server and give them permissions to your database? If not then all connections will be denied except for the database owner or SA.

You may consider creating an Active Directory Group and add all your users to that group. Then you only have to add the AD Group to the SQL Server logins, then grant that group the needed permissions. Then you can move users in and out of the AD group and they will automatically get the permissions as part of the group. Without seeing more of how your server and database is set up, I can only offer these generic hints. This may do the trick for you, though.

One thing to keep in mind...lax or weak database security is a huge vulnerability...if you have a competent DBA around, have him help you do this. If you don't, think very seriously about hiring a consultant for a few hours to help. Believe me, it's not for the faint of heart if you want to protect your data, and spending a few dollars now will save you much misery later.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.